Consulting

Results 1 to 4 of 4

Thread: Sqlserver database multipe tables data to access database

  1. #1
    VBAX Regular
    Joined
    Jan 2011
    Posts
    62
    Location

    Sqlserver database multipe tables data to access database

    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

  2. #2
    VBAX Newbie
    Joined
    Nov 2023
    Posts
    1
    Location
    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

  3. #3
    VBAX Regular
    Joined
    Sep 2023
    Posts
    78
    Location
    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
    Last edited by jdelano; 11-28-2023 at 10:54 AM.

  4. #4
    Quote Originally Posted by jacobye View Post
    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
    Can you provide source code reference?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •