Solved

MS SQL 2005 to Access 2003 via ODBC

Posted on 2009-05-13
2
464 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
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access printing shortcut in Ms Access 10 36
Correct ordering 16 48
Access Run-time error '6' Overflow 5 13
Merge Statement 3 9
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now