Solved

MS SQL 2005 to Access 2003 via ODBC

Posted on 2009-05-13
2
472 Views
Last Modified: 2012-05-06
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
Comment
Question by:hyphenpipe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24373405
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
 
LVL 3

Accepted Solution

by:
beaconlightboy earned 500 total points
ID: 24374386
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question