Updating MS Access Linked Table from VBS file

I am currently working on moving 100s of access databases from a variety of folders to another set of folders and need to update any references to linked tables that will be broken during the move. I have identified how to update the location of the linked database table by adding a macro to the access database itself by doing something like the following:

Dim tdf As TableDef, db As Database
Set db = CurrentDb
db.TableDefs.Refresh
For Each tdf In db.TableDefs
    ' My Logic for checking to see if it i开发者_开发知识库s is a linked 
    ' table and then updating it appropriately
Next
Set collTables = Nothing
Set tdf = Nothing
Set db = Nothing

However, I do not want to have to add the code to each of the access databases so I was wondering if there was a way to create a VBS file which would execute the same type of logic. I tried the following code, but I am getting the following error when the line with the for each logic is executed: "Arguments are of the wrong type, are out of acceptable range or are in conflict with one another"

Set MyConn = CreateObject("ADODB.Connection")
MyConn.Open "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = MyFile.mdb"

for each tblLoop in db.TableDefs
    ' business logic
next

set tblLoop = nothing
MyConn.close
set MyConn = nothing

I'm hoping that someone more familiar with doing this type of coding will be able to point me in the right direction. Is there a way to utilize the TableDefs table from outside of Access through a VBS file and if so, what would that code look like.

Thanks, Jeremy

---

You cannot use tabledefs with ADO, but you can open the database in VBScript:

Dim db ''As DAO.Database 
Dim ac ''As Access Application

''As noted by wmajors81, OpenDatabase is not a method of the application object
''OpenDatabase works with DBEngine: http://support.microsoft.com/kb/152400
Set ac = CreateObject("Access.Application")
ac.OpenCurrentDatabase("c:\test.mdb") 
Set db = ac.CurrentDatabase

For Each tdf In db.TableDefs

Etc.

If you have start up code or forms, or database passwords, you will run into some problems, but these can be overcome, for the most part, by simulating the shift key press. This would be easier, I think, in VBA than VBScript, but AFAIK it is possible in VBScript. database passwords can be supplied in the OpenDatabase action.

---

I was able to expand upon the answer by @Remou to come up with some code that worked. Part of his answer included the following statement which threw an error "Set db = ac.OpenDatabase". As far as I can tell "OpenDatabase" is not a valid method, but OPenCurrentDatabase is. Also, I was getting an error when trying to set db equal to the value returned by OpenCurrentDatabase so I'm assuming that it is a sub and not a function. However, I was able to get access to the Current Database by utilizing ac.CurrentDB once I had established the connection to the the database utilizing OpenCurrentDatabase

Dim db ''As DAO.Database 
Dim ac ''As Access Application

Set ac = CreateObject("Access.Application")
ac.OpenCurrentDatabase("D:\delete\UpdatingLinkedTableInAccess\GrpLfRsvs201108.mdb")

set db = ac.CurrentDB

For Each tdf In db.TableDefs
        With tdf
            If Len(.Connect) > 0 Then
                If Left(.Connect, 4) = "ODBC" Then
                ' ignore these are connected via ODBC and are out of scope
                Else
                        ' biz logic
                End If
            End If
        End With
next

set db = nothing
ac.Quit
set ac = nothing

Thanks again @Remou for your assistance.

---

You don't need to create an Access application instance. Use DBEngine and DAO.Workspace instead.

Option Explicit
Dim db
Dim dbe
Dim strDbPath
Dim tdf
Dim wrkJet

strDbPath = "C:\Access\webforums\whiteboard2003.mdb"

Set dbe = CreateObject("DAO.DBEngine.36")
Set wrkJet = dbe.CreateWorkspace("", "admin", "", 2) ' dbUseJet = 2
' exclusive = True and read-only = False '
Set db = wrkJet.OpenDatabase(strDbPath, True, False)

For Each tdf In db.TableDefs
    If Left(tdf.Connect, 10) = ";DATABASE=" Then
        WScript.Echo tdf.Connect
    End If
Next
db.Close
Set db = Nothing
Set wrkJet = Nothing
Set dbe = Nothing

You would need "DAO.DBEngine.120" for ACCDB format database.

If you're using a database password, include it in OpenDatabase.

Set db = wrkJet.OpenDatabase(strDbPath, True, False, ";pwd=password")