Using ADOX code to link to backend

[Max Moor]

Hi All,
I was pointed to some code for linking to my backend using ADOX
code (thanks, Doug). I was also pointed to code using the Windows API
to open the file box. That part works fine, and returns the path to the
backend correctly. With a few edits, the ADOX code is below...


Public Sub RefreshTableLinks()
Dim cn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim strBackend As String


strBackend = GetFileName

-> cn.Open CurrentProject.Connection

' Open the catalog.
cat.ActiveConnection = cn
For Each tbl In cat.Tables
tbl.Properties("Jet OLEDB:Link Datasource") = strBackend
Next

cn.Close

End Sub


When I call cn.Open, I get the message:

"The database has been placed in a state by user 'Admin' on machine
'MAX' that prevents it from being opened or locked."

Can anyone give me a clue what I need to do differently? I
changed the original connection string to just use the current project
connection. Is that valid?

- Max

 

[prabha]

Hi Max,

You are opening the same project you are currently in when doing
cn.Open CurrentProject.Connection

Why?

Use this sample code (if you haven't already seen this):
ACC2000: How to Use ADOX to Create and Refresh Linked Jet Tables
http://support.microsoft.com/default.aspx?scid=kb;en-us;275249

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."


--------------------
| Subject: Using ADOX code to link to backend
| From: Max Moor <maxmoor@remove_hotmail.com>
| Message-ID: <[email protected]>
| User-Agent: Xnews/5.04.25
| Newsgroups: microsoft.public.access.modulesdaovba.ado
| Date: Wed, 04 Feb 2004 11:06:10 -0800
| NNTP-Posting-Host: wbar2.sea1-4-12-156-058.dsl-verizon.net 4.12.156.58
| Lines: 1
| Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.
phx.gbl
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.modulesdaovba.ado:14658
| X-Tomcat-NG: microsoft.public.access.modulesdaovba.ado
|
| Hi All,
| I was pointed to some code for linking to my backend using ADOX
| code (thanks, Doug). I was also pointed to code using the Windows API
| to open the file box. That part works fine, and returns the path to the
| backend correctly. With a few edits, the ADOX code is below...
|
|
| Public Sub RefreshTableLinks()
| Dim cn As New ADODB.Connection
| Dim cat As New ADOX.Catalog
| Dim tbl As New ADOX.Table
| Dim strBackend As String
|
|
| strBackend = GetFileName
|
| -> cn.Open CurrentProject.Connection
|
| ' Open the catalog.
| cat.ActiveConnection = cn
| For Each tbl In cat.Tables
| tbl.Properties("Jet OLEDB:Link Datasource") = strBackend
| Next
|
| cn.Close
|
| End Sub
|
|
| When I call cn.Open, I get the message:
|
| "The database has been placed in a state by user 'Admin' on machine
| 'MAX' that prevents it from being opened or locked."
|
| Can anyone give me a clue what I need to do differently? I
| changed the original connection string to just use the current project
| connection. Is that valid?
|
| - Max
|

 

[CSmith]

Hi,

I didn't see where you were actually setting your connection variable after
declaring it.

Example:

Set cn = Application.CurrentProject.Connection

 

[Max Moor]

(e-mail address removed) ("prabha") wrote in @cpmsftngxa07.phx.gbl:

http://support.microsoft.com/default.aspx?scid=kb;en-us;275249

Aha-a-a-a! Works like a charm. I had been using the code at:

http://support.microsoft.com/?id=230588

I must have messed something up. Anyway, thanks, Eric!

- Max