Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

MS SQL 2005 to Access 2003 via ODBC

I have a database in MS SQL Server 2005 that from time to time needs to have some tables brought into Microsoft Access.

I have no problem doing this using a DSN and importing but every time I do it I have to make a new Access database and set up my table relationships all over again which is time consuming.

Is there a way I can ideally ust update my current access database with the new records from my SQL Server 2005 database, or if not somehow save my relationships so I can apply them to the new Access Database?

Any help, thoughts, suggestions is appreciated.
0
hyphenpipe
Asked:
hyphenpipe
1 Solution
 
Kelvin SparksCommented:
I'm assuming that using linked table is not an option.

One practice I have used is to link all the tables you need, then create a local copy & create all the relations ships then have a process to delete the data from each local table in turn and then repopulate (delete and insert queries from the linked tables).

You must get the delete order and insert order correct so that relationships do not hinder the process.


Kelvin
0
 
beaconlightboyCommented:
I have a couple databases where access 2003 is the front end and the back end resides on SQL.  The best solution IMO is to create a table in the SQL server that lists what tables to link to.  Simply link this table in access, then using ADOX class you can iterate thru the linked table and create new links to all the tables in the sql server.  What's nice is you can do this on startup of the database and never have to worry about users having bad links.   On startup we dynamically delete all links access knows about, then create new ones.

I have attached some code that you can review.
' These Constants are used to make the initial contact with the Personnel Control database on the SQL Server.
'   If the main DSN (for the SQL Server) or the Main Table name are ever changed then changing these constants should
'   be all that is needed to keep things running properly
'
Private Const PRODUCTION_DSN = "ODBC;DSN=Personnel;Description=Personnel database;" & _
                                "APP=Microsoft Office 2003;DATABASE=Personnel;" & _
                                "Trusted_Connection=Yes"
                                
Private Const DEVELOPMENT_DSN = "ODBC;DSN=Personnel_DEV;Description=Personnel database;" & _
                                "APP=Microsoft Office 2003;DATABASE=Personnel_DEV;" & _
                                "Trusted_Connection=Yes"
 
Private Const PRODUCTION_CONNECTION_STRING = "Provider=sqloledb;" & _
                                                "Data Source=PA;" & _
                                                "Initial Catalog=Personnel;" & _
                                                "Integrated Security=SSPI"
                                                
Public Const DEVELOPMENT_CONNECTION_STRING = "Provider=sqloledb;" & _
                                                "Data Source=PA;" & _
                                                "Initial Catalog=Personnel_Dev;" & _
                                                "Integrated Security=SSPI"
 
Private DSN As String
Private Progress As Form_ProgressBar
 
Public Function LinkProductionTables() As Boolean
 
    SetADOConnectionString (PRODUCTION_CONNECTION_STRING)
    DSN = PRODUCTION_DSN
    LinkTables
    
End Function
 
Public Function LinkDevelopmentTables() As Boolean
    
    SetADOConnectionString (DEVELOPMENT_CONNECTION_STRING)
    DSN = DEVELOPMENT_DSN
    LinkTables
    
End Function
 
Private Function LinkTables() As Boolean
On Error GoTo HandleException
 
    Set Progress = New Form_ProgressBar
    Progress.title = "Please wait while the database initializes.."
    
    Call RemoveLinks  'removes table links
    LinkMasterControlTables
    LinkSQLServerTables
    CreateTemporaryTables
    
    Progress.MessageText = "COMPLETE: Linked Control Tables."
    Set Progress = Nothing
    
ExitFunction:
       Exit Function
    
HandleException:
    MsgBox err.description
    GoTo ExitFunction
    
End Function
'
'   The Master control table holds the table names of other tables which are part of the BLCMS Control system.
'
Private Function LinkMasterControlTables()
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim ControlTables As Variant
    
    Set cat = New ADOX.Catalog
    
    'Open the Catalog
    cat.ActiveConnection = CurrentProject.Connection
    
    ControlTables = GetControlTableNames
    
    ' Create the new Table
    For Each tableName In ControlTables
        Set tbl = New ADOX.Table
        Set tbl.ParentCatalog = cat
    
        s = Right(tableName, Len(tableName) - 4)
        tbl.name = tableName
        ' Set the properties to create the link
        tbl.Properties("Jet OLEDB:Link Provider String") = DSN
        tbl.Properties("Jet OLEDB:Remote Table Name") = Right(tableName, Len(tableName) - 4)
        tbl.Properties("Jet OLEDB:Create Link") = True
            
        ' Append the table to the tables collection
        cat.Tables.Append tbl
        Set tbl = Nothing
    Next
        
    DoEvents
        
    Set cat = Nothing
End Function
Public Sub UnlinkControlTables()
    Dim ControlTables As Variant
    
    ControlTables = GetControlTableNames() ' Defined in the Globals module
    
    For Each tble In ControlTables
        DoCmd.DeleteObject acTable, tble
    Next
    
End Sub
 
Private Sub RemoveLinks() ' Unlink Tables
 
    Dim tbl As TableDef
    Dim ctlCurrentControl
    
'    Set frm = New Form_FRM_ProgressBar
    
    Progress.MaxValue = CurrentDb.TableDefs.Count
    Progress.Visible = True
    
    For Each objTable In Application.CurrentDb.TableDefs
        If TableShouldBeDeleted(objTable.name) Then
            DoCmd.DeleteObject acTable, objTable.name
            Progress.MessageText = "DELETED: " & objTable.name
        End If
        Progress.Progress = Progress.Progress + 1
    Next
    
    DoEvents
    UnlinkControlTables
End Sub
 
Private Function TableShouldBeDeleted(ByVal tableName As String) As Boolean
    Dim ControlTables As Variant
    Dim IsControlTable As Boolean
    
    ' If tableName is either a System Table or a Local Temporary Table we
    '   don't want to remove it
    ControlTables = GetControlTableNames() ' Defined in the Globals module
    
    For Each tble In ControlTables
        IsControlTable = (tableName = tble)
        If IsControlTable Then Exit For
    Next
    
    TableShouldBeDeleted = Not ((Left(tableName, 4) = "MSys") Or IsLocalTemporaryTable(tableName) Or IsControlTable)
End Function
 
Private Function IsLocalTemporaryTable(ByVal tableName As String) As Boolean
    IsLocalTemporaryTable = False
    Dim DBS As Database
    Dim rst As DAO.Recordset
    
    Set DBS = CurrentDb
    Set rst = DBS.OpenRecordset("dbo_LocalTemporaryTables", dbOpenDynaset, dbSeeChanges)
    
    
    With rst
    .MoveFirst
        Do Until .EOF
        
            If tableName = .Fields("Name") Then
                IsLocalTemporaryTable = True
            End If
            
            .MoveNext
        Loop
    End With
    
    Set DBS = Nothing
    Set rst = Nothing
End Function
 
Private Function LinkSQLServerTables()
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim qdf As QueryDef
    '
    Dim DBS As Database
    Dim rst As DAO.Recordset
    
    '-------------------------------------------------------------
    
    Set DBS = CurrentDb
    Set rst = DBS.OpenRecordset("dbo_LinkedSQLServerTables", dbOpenDynaset, dbSeeChanges)
    '
    Set cat = New ADOX.Catalog
    '-------------------------------------------------------------
      
    'Open the Catalog
    cat.ActiveConnection = CurrentProject.Connection
    rst.MoveLast
    Progress.MaxValue = rst.RecordCount
    Progress.Progress = 0
    
    
    With rst
        .MoveFirst
        Do Until .EOF
            Set tbl = New ADOX.Table
            Set tbl.ParentCatalog = cat
             'Create the new Table
            tbl.name = .Fields("Name")
            
             'Set the properties to create the link
            tbl.Properties("Jet OLEDB:Link Provider String") = DSN
            tbl.Properties("Jet OLEDB:Remote Table Name") = "dbo." & .Fields("Name")
            tbl.Properties("Jet OLEDB:Create Link") = True
            
            'Append the table to the tables collection
            cat.Tables.Append tbl
            
            Progress.MessageText = "LINKED: " & .Fields("Name")
            Progress.Progress = Progress.Progress + 1
            
            DoEvents
            
            .MoveNext
            
            Set tbl = Nothing
        Loop
    End With
    
    Set cat = Nothing
    Set qdf = Nothing
    Set DBS = Nothing
    Set rst = Nothing

Open in new window

0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now