Sub ConnectToSQLServer() Dim conn As Object Dim rs As Object Dim strSql As String Dim strConn As String Dim db As DAO.Database Dim tdf As DAO.TableDef Dim tableName As String ' Connection string to connect to SQL Server strConn = "Provider=SQLOLEDB;Data Source=Server;Initial Catalog=TestDataBase;User ID=user;Password=password;" ' Create a connection object Set conn = CreateObject("ADODB.Connection") ' Open the connection conn.Open strConn ' Access Database object Set db = CurrentDb ' SQL query to retrieve table names strSql = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'" ' Create a recordset object and execute the query Set rs = conn.Execute(strSql) ' Loop through the recordset to get table names 'Do While Not rs.EOF ' Display table names (replace MsgBox with desired processing) ' MsgBox rs("table_name") ' rs.MoveNext ' Loop ' Loop through the recordset to create linked tables in Access Do While Not rs.EOF tableName = rs.Fields(0).value ' Check if the table doesn't exist in Access 'If Not TableExists(tableName, db) Then Set tdf = db.CreateTableDef(tableName) tdf.SourceTableName = tableName tdf.Connect = conn.connectionString db.TableDefs.Append tdf ' End If rs.MoveNext Loop ' Close the recordset and connection rs.Close conn.Close ' Clean up objects Set rs = Nothing Set conn = Nothing Set db = Nothing Exit Sub End Sub
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)