DSN-Less Connection with multiple tables

Question

Right now I have an MS SQL database with about 50 or so tables in it with which I'd like to link to MS Access using a DSN Less connection. Below is the basic code where I have a parameter of stRemoteTableName which is the table name of the SQL table to import. I could call this function each time for each table but that would take forever; is there anyway to loop through all tables in an SQL database and pass them to this function so that all tables are imported? I'm having a very hard time finding any code online for something like this, so help is much appreciated.

 Private Sub ImportAllTables(stRemoteTableName)
        On Error GoTo AttachDSNLessTable_Err
            Dim td As TableDef
            Dim stConnect As String
            stServer = "C:\Location"
            stDatabase = "DB"
            stLocalTableName = stRemoteTableName
            stUsername = ""

            For Each td In CurrentDb.TableDefs
                If td.Name = stLocalTableName Then
                    CurrentDb.TableDefs.Delete stLocalTableName
                End If
            Next

            stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"

            Set td = CurrentDb.CreateTableDef(stLocalTableName, stRemoteTableName, stConnect)
            CurrentDb.TableDefs.Append td
            Exit Sub

        AttachDSNLessTable_Err:

           AttachDSNLessTable = False
            MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description
        End Sub

You can query for all the table names: stackoverflow.com/questions/3913620/… — Tim Williams
– Tim Williams, Commented Jul 8, 2013 at 22:04

HansUp · Accepted Answer · 2013-07-08 22:04:39Z

1

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.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strConnect As String
Dim strSelect As String

strSelect = "SELECT t.name FROM sys.tables t;"
'strConnect =  <you already have this as stConnect>
Set db = CurrentDb
Set qdf = db.CreateQueryDef("", strSelect)
qdf.Connect = strConnect
Set rs = qdf.OpenRecordset
With rs
    Do While Not .EOF
        ' you want to link; I will just list
        ' the table names
        Debug.Print !name
        .MoveNext
    Loop
    .Close
End With
Set rs = Nothing
Set qdf = Nothing
Set db = Nothing

answered Jul 8, 2013 at 22:04

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

codingManiac Over a year ago

Thank you very much, I'm always learning, only going into my junior year of college and the only programming I've done is VB and VBA (very limited).

HansUp Over a year ago

You're welcome. Most of that is fairly standard Access VBA. Pass-through queries seem to be less familiar to many people AFAICT. Did it all make sense to you?

codingManiac Over a year ago

Yes I'm understanding it better now that I'm seeing your code and looking at some of the stuff on MS support, but I'd have to agree with you; I've never had to deal with pass-through queries even though I've heard of them.

Collectives™ on Stack Overflow

DSN-Less Connection with multiple tables

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related