Test If Access Table Is Open?

From Excel 2007, I am pulling data from a 2007 Access table
(regardless if db is open or not) to dump it into a spreadsheet. I
want to test if the Access table (not just the db) is open before
importing the data. The closest thing I have found is the Access
sysCmd object and the property acObjStateOpen. What I need is an
equivalent that I can run in Excel. Does anybody know how to test
(from within Excel) if an Access table is open?
0
11/24/2011 2:09:42 PM
excel 39879 articles. 2 followers. Follow

8 Replies
443 Views

Similar Articles

[PageSpeed] 36

hi,

Sub test()
Dim MyFile As String
MyFile = "C:\Program Files\Microsoft Office\Office10\Samples\Comptoir.mdb"
     If Not IsFileOpen(MyFile) Then
         MsgBox "file is NOT open"
     Else
         MsgBox "file is open"
     End If
End Sub

Function IsFileOpen(FileName As String)
     Dim iFilenum As Long
     Dim iErr As Long

     On Error Resume Next
     iFilenum = FreeFile()
     Open FileName For Input Lock Read As #iFilenum
     Close iFilenum
     iErr = Err
     On Error GoTo 0

     Select Case iErr
     Case 0:    IsFileOpen = False
     Case 70:   IsFileOpen = True
     Case Else: Error iErr
     End Select

End Function


-- 
isabelle



Le 2011-11-24 09:09, Dave a �crit :
>  From Excel 2007, I am pulling data from a 2007 Access table
> (regardless if db is open or not) to dump it into a spreadsheet. I
> want to test if the Access table (not just the db) is open before
> importing the data. The closest thing I have found is the Access
> sysCmd object and the property acObjStateOpen. What I need is an
> equivalent that I can run in Excel. Does anybody know how to test
> (from within Excel) if an Access table is open?
0
isabelle
11/24/2011 2:45:27 PM
On Nov 24, 9:45=A0am, isabelle <i...@v.org> wrote:
> hi,
>
> Sub test()
> Dim MyFile As String
> MyFile =3D "C:\Program Files\Microsoft Office\Office10\Samples\Comptoir.m=
db"
> =A0 =A0 =A0If Not IsFileOpen(MyFile) Then
> =A0 =A0 =A0 =A0 =A0MsgBox "file is NOT open"
> =A0 =A0 =A0Else
> =A0 =A0 =A0 =A0 =A0MsgBox "file is open"
> =A0 =A0 =A0End If
> End Sub
>
> Function IsFileOpen(FileName As String)
> =A0 =A0 =A0Dim iFilenum As Long
> =A0 =A0 =A0Dim iErr As Long
>
> =A0 =A0 =A0On Error Resume Next
> =A0 =A0 =A0iFilenum =3D FreeFile()
> =A0 =A0 =A0Open FileName For Input Lock Read As #iFilenum
> =A0 =A0 =A0Close iFilenum
> =A0 =A0 =A0iErr =3D Err
> =A0 =A0 =A0On Error GoTo 0
>
> =A0 =A0 =A0Select Case iErr
> =A0 =A0 =A0Case 0: =A0 =A0IsFileOpen =3D False
> =A0 =A0 =A0Case 70: =A0 IsFileOpen =3D True
> =A0 =A0 =A0Case Else: Error iErr
> =A0 =A0 =A0End Select
>
> End Function
>
> --
> isabelle
>
> Le 2011-11-24 09:09, Dave a =E9crit :
>
>
>
> > =A0From Excel 2007, I am pulling data from a 2007 Access table
> > (regardless if db is open or not) to dump it into a spreadsheet. I
> > want to test if the Access table (not just the db) is open before
> > importing the data. The closest thing I have found is the Access
> > sysCmd object and the property acObjStateOpen. What I need is an
> > equivalent that I can run in Excel. Does anybody know how to test
> > (from within Excel) if an Access table is open?- Hide quoted text -
>
> - Show quoted text -

I appreciate the code, but doesn't this simply test if the db
(database) is open? As mentioned, I need to drill down to the table.
It's OK to run the code in Excel if the database is open...just not
one specific table.
0
11/29/2011 1:14:35 AM
do you use ActiveSheet.QueryTables.Add ?

-- 
isabelle

0
isabelle
11/29/2011 3:16:33 AM
On Nov 28, 10:16=A0pm, isabelle <i...@v.org> wrote:
> do you use ActiveSheet.QueryTables.Add ?
>
> isabelle
part of what I have in Excel:

Set dbs =3D OpenDatabase("\\FullPath\myDatabase.mdb", , True)
Set rst =3D dbs.OpenRecordset("tblBalance", dbOpenDynaset, dbReadOnly)
Worksheets("tblBalance").Unprotect
Worksheets("tblBalance").Range("A2").CopyFromRecordset rst
'Worksheets("tblBalance").Protect
dbs.Close

I would like to block access to the table if it is open (say, for
maintenance). I don't think different record locking options can be
set for Access tables so I'm looking for an "is it open" test to be
run from Excel. There is a similar test for Access:
sysCmd(acSyscmdGetObjectState, acTable, "tblName") =3D acObjStateOpen
0
11/29/2011 11:56:09 PM
hi Dave,

i opened up the Comptoir.mdb file and i opened the table "Clients" for editing.
and on excel i have executed the macro "test" with no problem.
i also did a test when Comptoir.mdb have being closed, and there is no problem.

Sub test()
DAOCopyFromRecordSet "C:\Program Files\Microsoft Office\Office10\Samples\Comptoir.mdb", _
     "Clients", "Fonction", " = 'Propri�taire'", Range("A1")
End Sub


Sub DAOCopyFromRecordSet(DBFullName As String, TableName As String, _
     FieldName As String, Criteria As String, TargetRange As Range)

Dim db As Database, rs As Recordset
Dim intColIndex As Integer
     Set TargetRange = TargetRange.Cells(1, 1)
     Set db = OpenDatabase(DBFullName)

'    Set rs = db.OpenRecordset(TableName, dbOpenTable) ' all records
     Set rs = db.OpenRecordset("SELECT * FROM " & TableName & _
         " WHERE " & FieldName & Criteria, dbReadOnly) ' filter records

     ' write field names
     For intColIndex = 0 To rs.Fields.Count - 1
         TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
     Next

     ' write recordset
     TargetRange.Offset(1, 0).CopyFromRecordset rs

     Set rs = Nothing
     db.Close
     Set db = Nothing
End Sub



-- 
isabelle
0
isabelle
12/1/2011 3:18:09 AM
On Nov 30, 10:18=A0pm, isabelle <i...@v.org> wrote:
> hi Dave,
>
> i opened up the Comptoir.mdb file and i opened the table "Clients" for ed=
iting.
> and on excel i have executed the macro "test" with no problem.
> i also did a test when Comptoir.mdb have being closed, and there is no pr=
oblem.
>
> Sub test()
> DAOCopyFromRecordSet "C:\Program Files\Microsoft Office\Office10\Samples\=
Comptoir.mdb", _
> =A0 =A0 =A0"Clients", "Fonction", " =3D 'Propri=E9taire'", Range("A1")
> End Sub
>
> Sub DAOCopyFromRecordSet(DBFullName As String, TableName As String, _
> =A0 =A0 =A0FieldName As String, Criteria As String, TargetRange As Range)
>
> Dim db As Database, rs As Recordset
> Dim intColIndex As Integer
> =A0 =A0 =A0Set TargetRange =3D TargetRange.Cells(1, 1)
> =A0 =A0 =A0Set db =3D OpenDatabase(DBFullName)
>
> ' =A0 =A0Set rs =3D db.OpenRecordset(TableName, dbOpenTable) ' all record=
s
> =A0 =A0 =A0Set rs =3D db.OpenRecordset("SELECT * FROM " & TableName & _
> =A0 =A0 =A0 =A0 =A0" WHERE " & FieldName & Criteria, dbReadOnly) ' filter=
 records
>
> =A0 =A0 =A0' write field names
> =A0 =A0 =A0For intColIndex =3D 0 To rs.Fields.Count - 1
> =A0 =A0 =A0 =A0 =A0TargetRange.Offset(0, intColIndex).Value =3D rs.Fields=
(intColIndex).Name
> =A0 =A0 =A0Next
>
> =A0 =A0 =A0' write recordset
> =A0 =A0 =A0TargetRange.Offset(1, 0).CopyFromRecordset rs
>
> =A0 =A0 =A0Set rs =3D Nothing
> =A0 =A0 =A0db.Close
> =A0 =A0 =A0Set db =3D Nothing
> End Sub
>
> --
> isabelle

Tried your example (full path is same as what works now regardless of
tbl being open, just not shown here)

Sub test()
DAOCopyFromRecordSet "\\...dbname.mdb", _
     "tblBalance", "*", " =3D '*'", Range("A2")
End Sub

Sub DAOCopyFromRecordSet(DBFullName As String, TableName As String, _
     FieldName As String, Criteria As String, TargetRange As Range)

Dim db As Database, rs As Recordset
Dim intColIndex As Integer
     Set TargetRange =3D TargetRange.Cells(1, 1)
     Set db =3D OpenDatabase(DBFullName)
*     Set rs =3D db.OpenRecordset(TableName, dbOpenTable) ' all records
     'Set rs =3D db.OpenRecordset("SELECT * FROM " & TableName & _
         " WHERE " & FieldName & Criteria, dbReadOnly) ' filter
records
     ' write field names
     For intColIndex =3D 0 To rs.Fields.Count - 1
         TargetRange.Offset(0, intColIndex).Value =3D
rs.Fields(intColIndex).Name
     Next
     ' write recordset
     TargetRange.Offset(1, 0).CopyFromRecordset rs
     Set rs =3D Nothing
     db.Close
     Set db =3D Nothing
End Sub
I get an error (3219) "Application-defined or object-defined" message
at the line I marked with *. I noticed you do not dim the db or rs
specifically as DAO. I tried specific references and got the same
error. I do have a reference to DAO 3.6 and VBA 5.3. So why would this
work for you and not me?
0
12/2/2011 12:47:50 AM
hi Dave,

did you add a reference to Microsoft DAO x.x Object Library

-- 
isabelle

0
isabelle
12/2/2011 1:06:24 AM
have you tried a full path instead of (\ \ ...)

-- 
isabelle
0
isabelle
12/2/2011 1:53:38 AM
Reply:

Similar Artilces:

Outlook cannot access Global Address Book
Outlook 2000 & 2002 continue to lose the Global Address Book from the Exchange server. The location for the address book is somehow changed to either E or D. Outlook 2002 will also shutdown unexpectedly. ...

Only one user at a time can access OWA
Using Exchange 5.5 SP3 on W2K. Only one user can log on using OWA at any one time. As soon as the first user logs out the next can then log on, but in a organisation of 1300 - bit of a problem. Previously we did have things working fine with Exchange 5.5 on WinNT but a hacking attack put paid to that system! Have run through MS trouble shooting but found no mention of this problem anywhere else. Any suggestions welcomed Regards Mike Cameron cameronm@queensbury.beds.sch.uk (Mike Cameron) wrote: >Using Exchange 5.5 SP3 on W2K. Only one user can log on using OWA at >any one time. As...

ANOVA procedures and t-test equal variance
In looking through Excel I see that it has both the ANOVA procedure and a t-test with equal variances and a t-test for unequal variances. One of the assumptions that is made in both the "standard t test" and ANOVA is that the variances are equal. Yet, Excel seems to have no test for homoscedasicity (oh what a cool word). How do you know these procedures are ok to use if you have not checked the assumption of equal variances? Noosa - > In looking through Excel I see that it has both the ANOVA procedure and a > t-test with equal variances and a t-test for unequal vari...

Spreadsheet Is Full When Transofrming a SQL Table To Excel
Hi, I used to DTS of SQL2000 to create an Excel table. SQL table contains 136600 rows (255 characters each). Thank you very much mike mike2002am@yahoo.com ...

Compare (2) fields in (1) table
Grettings, I am working on a database analysis and need some assistance on building a specific query. I have an input table that was formed at a previous query that is composed of a few 100 records. In one column field I have "Meter_Number_1" and in the same table in a seperate column field I have "Meter_Number_2" What I need to do is for each row, to identify where the number for "Meter_Number_1" and "Meter_Number_2" are the same. In addition to that, a sperate query will be where they are not the same. I am starting off smal...

pivot tables formatting
I have a sales pivot with the months in columns and products by category in the rows. I simply want to have "normal" lines, which will make for easy reading like any other report at print time. Also I would like the product headers left aligned down in their column Whatever formatting I apply in whatever way I can select those items, my formatting is undone on a refresh, though the auto-format option is off in the table setup. It does keep seetings like the font and bold, etc, but lines and alignment instructions are rudely overwritten. Anybody with an answer to that one? I w...

Proofreading and Access 2003
Why is Word better than Access when I use several languages? -- gu1934 On Tue, 8 Jan 2008 02:25:01 -0800, gu1934 <gu1934@discussions.microsoft.com> wrote: >Why is Word better than Access when I use several languages? Ummm... Context? Perhaps because Word is a word processor, designed to handle words of language, and Access is a relational database development environment designed to handle arbitrary data; perhaps because of Microsoft corporate priorities. John W. Vinson [MVP] -- gu1934 "John W. Vinson" wrote: > On Tue, 8 Jan 2008 02:25:01 -0800, ...

Setting up a Credit card for online access
Using Money Small Business 2004. I had no problem setting up my citibank credit cards, but for some reason my DiscoverCard setup won't work. I am using the correct ID and passwords as I have tested them at the dicover site. Is there another location in Money 2004 where I can enter (and check) the connection and setup details for credit cards and banks? have you checked with discover card site for info? have you used the virtual account number for online purchases? very good item isn't it? >-----Original Message----- >Using Money Small Business 2004. I had no probl...

How can I add the "open with" as a choice to Outlook menus?
A co-worker with a mac sends me a .txt file attachment. If I 'preview' the file in Outlook, all the formatting is fine. However, if I open the attachment (default is to open a .txt file with notepad), the formatting is lost. I know I can either: 1) save the file to disk, and then from Windows explorer, do an "open with" to open it with Wordpad. 2) Change the file type associatein for .txt files so they will always open with wordpad. I don't like either of these choices. Option 1 adds extra steps (I may not want to save the file at all!). Option...

How do I attach/view/delete a word document to a form in Access?
We have create a customer log system using Access. Before the database crashed we were able attache files to a form. The Access database is used as a customer order log, viewed as a form. We would like to attach the cusomter order (word document) direclty to this record (form) so it could be reviewed easily. The command button would switch between attach file and view file. Next to it is another command button that would let you delete that file if there were a change. Any direction on how to recreate this function would be greatly appreciated. ...

Question: Access & Publisher??
Hi out there... I've an Access database (some data is names/addresses/etc)... and a Publisher template (with 8-10 Styles). I want to make a phone directory. I've tried googling "Access to Publisher"... and came back with alot of Access->Word hits... Didn't come up with anything useful on Publisher-FROM-Access either. Can someone point me to where I should start?? Thanks in advance, Steve What version Publisher? In earlier versions you can use a straightforward mail merge. In Publisher 2003-07 Catalog Merge would be good. Publisher recognizes Access databa...

test of wizard
-- This posting is provided "AS IS" with no warranties, and confers no rights. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=f61980a5-9a9d-4399-8044-95f7dc1810...

Edit a table while in form view
I have a form that has multiple tabs and subforms. On one of the tabs is a subform with a recordsource that is a query. It shows data from a table in datasheet mode. In that table I have a field "Selected" which is a Yes/No field (checkbox). In form view, I want to be able to check the checkbox in the table in the subform. I don't have that field locked, what other settings should I check? Thanks, Akilah Can you update fields in the query if you open the query by itself? You need to determine whether it is the form that is locking the fields or the query itself. ...

outlook mobile access broken
I noticed this morning that my OMA was give ASP application error reports when a device tried to connect. I put it down to changing the installed .net framework at the end of last week - using the aspnet_regiis.exe command located in the C: \WINDOWS\Microsoft.NET\Framework directory, I changed from v1.1.4322 to v2.0.50727. So I reverted back to v1.1.4322 hoping it would resolve the issue, no all I get is a 404 page not found - even though there are files there - now I'm a bit stumped. What is going to be the easiest way to fix/repair/recreate this virtual directory? Any pointers would ...

Hit testing
I'm trying to implement client area hit testing in a MDI application. In my document, I have a list of graphic objects (e.g. polygons) that are displayed in the view. The list can get pretty long, so the hit test algorithm needs to be fairly efficient. My first attempt at this was to add HitTest(CPoint) function to the graphic object class. In this function I created a Region and then tested it. Something like... int CPolygon::HitTest(CPoint Point) { CRgn Rgn; Rgn.CreatePolygonRgn(PointList.GetData(), PointList.GetSize(), WINDING); if (Rgn.PtInRegion(Point)) ...

ignore -- testing
Haven't seen a posting in several NGs since 6/25. Just curious if GG is updating these NGs. I'm using the "old" user interface. Can't find these NGs using the new GG "experience". just testing "joeu2004" <joeu2004@hotmail.com> wrote in message news:812e733c-2d95-4174-8a3d-d08ecc672145@e17g2000prj.googlegroups.com... Haven't seen a posting in several NGs since 6/25. Just curious if GG is updating these NGs. I'm using the "old" user interface. Can't find these NGs using the new GG "experience". testing a...

Multiple-criteria search on form, to open form
I know that there are queries that can do this, but I want to try to get this functionality on a form looking like this. After clicking 'Search', a table should return the filtered information. Please let me know if this is possible, and HOW to do it... preferably step by step? Thank you in advance. If all fields are empty, should return ALL records in table, correct? _______________________________ |Enter search criteria below | | ___________ | |Name | John | | | |__________| ...

HELP with excel table/chart
I have a question that I am almost sure has a very simple answer. have a small table with 3 columns. The first column is density and ha a set of values beneath the column heading. The second column is heigh and has a set of values beneath the column heading as well. However the is a second set of values in the third column that go with th heading for the second column and need to correspond with the value from the first column. I want to combine the values from the 2nd an 3rd columns so that I can make a scatter plot with one set of point for height. here is an example blah blah 2 1...

Embed Username & Password in Oracle External Linked Table
Looking for a way to embed a username & password for an external linked table for an Oracle database. When creating the connection you get the standard user/pass and database but no option to embed. Any ideas? On Thu, 4 Mar 2010 19:19:02 -0800, Rotund <Rotund@discussions.microsoft.com> wrote: Did you check with connectionstrings.com for the right connection string? -Tom. Microsoft Access MVP >Looking for a way to embed a username & password for an external linked table >for an Oracle database. When creating the connection you get the standard >...

Bookmark code not working in Access 2007
I have a database that tracks accidents. There are about 800 employees. Each employee in the employee table called, "tblEmployees" has an id number (this is the primary key - called EmpNumber, data type is text). This table is related to a table called, "tblAccidentInvestData". The related foreign key field in this table is called, "EMPNUM". The primary key in tblAccidentInvestData is called, "AccidentInvestID". A main data entry form called, "frmInputAccidentInvest" has a subform called, "subfrmAccidentInvest". This subform puts...

Testing an Exchange backup
Hello everyone I have been backing up my Exchange database (SBS 2003) using an online backup with the NTBackup utility, and everything appears to be working fine: backup files created in the right place at the right time, and a log file saying that the backup was successful. However, I would like to make sure that the backup has worked by using it to restore data. My problem is I want to be absolutely sure that if everything goes wrong and I can't restore, that I won't lose any data. I wonder whether the following plan would work? 1. Dismount my databases. 2. Rename my priv1.e...

Moving Gmail Acct to Outlook? Passes Test but won't work Why/Fix?
I have followed the directions on the gmail page on moving my acct to outlook. It passes the test and yet does not work. What gives? How do I fix? Is this some weirdness between google and microsoft. Any suggestions? Help! Could you be a little more specific? What doesn't work? Does it send? Doesn't receive? Do you get any error messages? What version of Outlook? -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP Roland Schorr & Tower http://www.rolandschorr.com Microsoft OneNote FAQ: http://www.factplace.com/onenotefaq.htm **I apologize but I am unable to respond to direct r...

SDK Access to PickLists
Hi, I am wondering whether anyone has done any work with accessing picklist values through the SDK. I can't find any reference for it in the documentation, but am sure that somebody would have tried it before me. Essentially, I am coding up asp custom server controls to mimic the controls in CRM. I wish to create a picklist control, and would like it to retrieve the list of available options and values from CRM, rather than me have to replicate the data (either in a separate SQL db, or in the control itself). If anyone knows how to retrieve the picklist values from within the crm data...

Open Workbooks
Hello, How do I determine using VBA code how many workbooks are open? Thanks. Bill ...

Long time to save or open a form
hi all I have a problem when opening a form or save the code behind . I am using Access XP with Windows XP on machine P4 3.4 GHz, 1.5 GB RAM the problem is that access take too much time to save or to open a form ,any idea to solve this problem is appreciated thanks in advance Wael Hi Wael, Is there a network that separates your computer from the actual .mdb file? If so, my advice is to only do design changes on a local copy. You might also try the undocumented /decompile switch. First, make a backup copy of your database, just in case something horribly wrong happens. With Acce...