Change a Database in another file 1

[MacroAlan]

We have many Access databases in which I need to add a new field to specific tables. The process needs to be repeatable because we will be refreshing our DEV environment many times before we go to production next year.

I have a table with the paths, table names, and tables to be changed.

Here is what I have so far:

Dim DB      As DAO.Database
Dim Con     As ADODB.Connection

Sub OpenDBInsertField()
    Dim RS      As DAO.Recordset
    Dim RemDB   As DAO.Database
    Dim strSQL  As String
    Set DB = CurrentDb()
    Set Con = CurrentProject.Connection
    
    Set RS = DB.OpenRecordset("tbl_Tables_Paths")
    RS.MoveFirst
    Do While Not RS.EOF
      [highlight #FF0000]'  Set RemDB = OpenDatabase(RS!AbsPath & RS!DBname, dbDriverNoPrompt, False)  'This not working[/highlight]
        strSQL = "ALTER TABLE " & RS!TableName & " ADD COLUMN QueueID TEXT (50)"
        Con.BeginTrans
            Con.Execute strSQL
        Con.CommitTrans
        
    RS.MoveNext
    Loop
    Set RS = Nothing
    Set Con = Nothing
    Set RemDB = Nothing
End Sub

Do I need to link to the table before I can alter it?

My working DB is Access 2010, the ones to be changed are generally MDB


Alan

 

[dhookom]

You shouldn't need to link to the remote db. This is code that has worked for me:

Set dbRemote = OpenDatabase(strRemoteMDB, , 0)

Does this fail from the first record or subsequent?

Have you tried adding the following to make sure the name is correct?

debug.Print RS!AbsPath & RS!DBname

Also, your Con is the current db not the remote. I would try

RemDB.Execute strSQL, dbFailOnError

Duane
Hook'D on Access
MS Access MVP

 

[MacroAlan]

Two minor changes I needed in the end. Added a field to my "tbl_Tables_Paths" table to hold passwords.

Need to tell OpenDatabase what kind I was using:

 set RemDB=OpenDatabase(RS!absPAth & RS!DBname, True,False,"MS Access;PWD=" & RS!Password)

Alan