Sqlserver database multipe tables data to access database

[farrukh]

Hi Gurus,
I have a VBA code taken from
https://www.wiseowl.co.uk/blog/s285/linkviewsvba.htm
The code works very fine to get the data from sqlserver database (single table data) to Access database.
Can it be done on multiple tables I mean to fetch the multiples table data of sqlserver to multiple tables of access database?




' we will need to create this table using DAO
Dim tdf As DAO.TableDef
' Some variable to make the code more generic
Dim strConnectionString As String
Dim strNameInAccess As String
Dim strNameInSQLServer As String
Dim strKey As String
' set the connection string
strConnectionString = "ODBC;DRIVER=SQL Server; " & _
"SERVER=.\SQLExpress;DATABASE=MyDatabase;Trusted_Connection=Yes"
' specify the tables you want to link. The table can be
' known by a different name in Access than the name in SQL server
strNameInAccess = "tblYacht"
strNameInSQLServer = "tblSailingBoat"
' specify the key field
strKey = "SailingBoatID"
' Delete the table from the local database if it exists
On Error Resume Next
DoCmd.RunSQL "drop table " & strNameInAccess
On Error GoTo 0
' Create a table using DAO give it a name in Access.
' Connect it to the SQL Server database.
' Say which table it links to in SQL Server.
Set tdf = CurrentDb.CreateTableDef(strNameInAccess)
tdf.Connect = strConnectionString
tdf.SourceTableName = strNameInSQLServer
' Add this table Definition to the collection
' of Access tables
CurrentDb.TableDefs.Append tdf
' Now create a unique key for this table by
' running this SQL
On Error Resume Next
DoCmd.RunSQL "CREATE UNIQUE INDEX UniqueIndex ON " _
& strNameInAccess & " (" & strKey & ")"
On Error GoTo 0

[jacobye]

You may need to adjust the code to handle the structure and relationships between multiple tables accurately. Depending on the complexity of your database schema and requirements, you might need to consider using more advanced techniques or frameworks. drive mad 2 (https://drivemad2.io/)

[jdelano]

I think this will get you close; I don't have an SQL Server I can test against. Add Microsoft ActiveX Data Objects to references in Tools



Private Sub btnCopyTables_Click()
' we will need to create this table using DAO
Dim tdf As DAO.TableDef
Dim sqlUserTablesRS As ADODB.Recordset
Dim sqlConn As ADODB.Connection
Dim connectionString As String
Dim tableName As String
Dim tablePrimaryKey As String
Dim SQL As String
' set the connection string
'strConnectionString = "ODBC;DRIVER=SQL Server; SERVER=.\SQLExpress;DATABASE=MyDatabase;Trusted_Connection=Yes"
connectionString = "Provider=SQLOLEDB;Data Source=.\SQLExpress;DATABASE=MyDatabase;Trusted_Connection=Yes;Connect Timeout=15"
Set sqlConn = New ADODB.Connection
Set sqlUserTablesRS = New ADODB.Recordset
' connect to SQL Server and retrieve the user tables
sqlConn.Open (connectionString)
' retrieves the tables and their primary indexes from the sql server
SQL = "select "
SQL = SQL & " s.name as SchemaName, "
SQL = SQL & " t.name as TableName, "
SQL = SQL & " tc.name as ColumnName, "
SQL = SQL & " ic.key_ordinal as KeyOrderNr "
SQL = SQL & "from "
SQL = SQL & " sys.schemas s "
SQL = SQL & " inner join sys.tables t on s.schema_id=t.schema_id "
SQL = SQL & " inner join sys.indexes i on t.object_id=i.object_id "
SQL = SQL & " inner join sys.index_columns ic on i.object_id=ic.object_id and i.index_id=ic.index_id "
SQL = SQL & " inner join sys.columns tc on ic.object_id=tc.object_id and ic.column_id=tc.column_id "
SQL = SQL & "where i.is_primary_key=1 "
SQL = SQL & "order by t.name, ic.key_ordinal ;"
sqlUserTablesRS.Open SQL, sqlConn
' loop through the tables and add them to access using the same names
Do While Not sqlUserTablesRS.EOF
tableName = sqlUserTablesRS("TableName")
tablePrimaryKey = sqlUserTablesRS("ColumnName")
On Error Resume Next
' remove it if it exists in this database
DoCmd.RunSQL "drop table " & tableName
On Error GoTo 0
Set tdf = CurrentDb.CreateTableDef(tableName)
tdf.Connect = strConnectionString
tdf.SourceTableName = tableName
CurrentDb.TableDefs.Append tdf
Set tdf = Nothing
On Error Resume Next
DoCmd.RunSQL "CREATE UNIQUE INDEX UniqueIndex ON " & tableName & " (" & tablePrimaryKey & ")"
On Error GoTo 0
sqlUserTablesRS.MoveNext
Loop
sqlUserTablesRS.Close
Set sqlUserTablesRS = Nothing
sqlConn.Close
Set sqlConn = Nothing
End Sub


EDIT: copied the wrong SQL statement for the table/index list

[YasminKelleb]

You may need to adjust the code to handle the structure and relationships between multiple tables accurately. Depending on the complexity of your database schema and requirements, you might need to consider using more advanced techniques or frameworks. rice purity test (https://ricepuritytest2024.com/)Can you provide source code reference?