Loop through tables in separate database - postgresql/ms sql server/others

[jaryszek]

Hi,



I would like to import all tables from external database using VBA.

My present code:

Function RemoveAndConnectToOdbcTables()


Dim username, pass, strConnect, strConnectQuery As String
Dim i As Long


'''You can use a pass-through query to list the table names from your SQL Server database. Open a recordset based on that query. Then loop through the recordset rows and link each table.
On Error GoTo ErroHandler


strConnect = ConnSringFunction
''username = "admin"
''pass = "PasswordAccess"


 DoCmd.Hourglass True
    Dim db As DAO.Database
    Dim tdef As TableDef
    Dim qdef As QueryDef
    Dim missingTbl() As String
    Dim upper As Integer
    Dim strMsg As String
    Dim tdfCurrent As TableDef
    Dim TableName As String
    Dim CollTables As New Collection
    Dim DicTables As Object
    Dim CollItem As Variant


    Set DicTables = CreateObject("Scripting.dictionary")

  ''Here i am retriving list of all tables from database as collection
    Set CollTables = SetCollTables(strConnect)
    
    strConnect = "ODBC;" & strConnect

'' I am looping through present database and based on that i am creating dictionary with full list of tables
    Set db = CurrentDb
    For i = 0 To CurrentDb.TableDefs.Count - 1
        Set tdef = db.TableDefs(i)
        If Len(tdef.Connect) > 0 And Left(tdef.Name, 1) = "t" And tdef.Name <> "t_currentschema" And tdef.Name <> "t_schemas" Then
            DicTables.Add tdef.Name, 1
            DoCmd.DeleteObject acTable, tdef.Name
        End If
    Next i


    Set tdef = Nothing

''Here based on added tables into dictionary i am creating new tables. 


    For Each CollItem In CollTables


        If DicTables.exists(CollItem) Then
            Set tdfCurrent = db.CreateTableDef(CollItem)
            tdfCurrent.Connect = strConnect
            tdfCurrent.SourceTableName = CollItem
            db.TableDefs.Append tdfCurrent
            db.TableDefs.Refresh
        End If


    Next CollItem


    For Each qdef In CurrentDb.QueryDefs
    Debug.Print qdef.Name
        If qdef.Type = dbQSQLPassThrough Then
            qdef.Connect = strConnect
        End If
    Next qdef


    DoCmd.Hourglass False


Exit Function




ErroHandler:
MsgBox "Error number is: " & Err.Number & "; " & Err.Description
DoCmd.Hourglass False


 End Function

In code above i am using present linked tables in database to append new linked table based on existing ones.
I want to:
1. Delete all existing linked tables
2. Entirely linked to database and create new linked tables from there

Please help,
Jacek

[Gicu]

Hi Jacek,

I think you over complicate it. I use a local Access table (hlpTables) where I list all my linked tables from various databases. Then in a loop I check for len(tdf.Connect)>0 and delete all linked tables followed by another loop based on hlpTables where I link them back.


Cheers,
Vlad

[ssanfu]

@jaryszek

In your code, you have

Function RemoveAndConnectToOdbcTables()


Dim username, pass, strConnect, strConnectQuery As String
Dim i As Long

Be aware that strConnectQuery is declared as a String and username, pass and strConnect are declared as variants.

If you want all 4 to be declared as strings, you MUST explicitly declare each variable as a string type.

Function RemoveAndConnectToOdbcTables()


Dim username As String, pass As String, strConnect As String, strConnectQuery As String
Dim i As Long