I have a puzzle here to connect to either SQL Server or Access Database (1 Viewer)

[nector]

Current I have created two databases in MS. Access and SQL Server with everything similar that is same tables no differences whatsoever. Now because we are now required to use SQL Server as the group database which means that all the 70 branches must use the internet permanently to remain connected. Due to poor connectivity in some branches located in the rural areas there is need to store data in Ms Access temporarily and then link back to SQL server when the connectivity is stable. Do not worry about how to shift the stored data in access database to SQL Server, I have a solution on that one, the problem here is how to create an option to connect either to access database or SQL Server with VBA that is where my puzzle is.

The access database here is used to store sales data only and then transfer that data to sql server as mentioned above.

(1) Is there a way to create an option to allow users to connect either to SQL Server OR Access database, example currently we are using this VBA below to SQL Server

Private Sub Form_Open(Cancel As Integer)
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = "ODBC;DRIVER=SQL Server; " & _
"SERVER=NECTORPRIME\SQLExpress;DATABASE=Accounting;Trusted_Connection=Yes"
tdf.RefreshLink
End If
Next tdf
Set tdf = Nothing
Set dbs = Nothing
If (Date > #12/31/2022#) Then
MsgBox "Your database has expired contact the developer." & vbCrLf & "Please contact us at our official email address" & vbCrLf & "or at 260977352401 for the latest version.", vbInformation + vbOKOnly, "Subscription Expired!"
Cancel = True
Application.Quit
Exit Sub
End If
End Sub

(2) Is it possible to store the SQL tables connection strings in a table so that the Access linked strings to the Access database remain undisturbed. But again, how do I now use VBA to use the stored SQL Server string in a local table to connect back to SQL Server.

 

[theDBguy]

To connect/link to a table in either Access or SQL Server, you will simply adjust the connection string. As you can see, the connection string to SQL Server starts out with "ODBC;". For Access databases, the connection string will start out with ";DATABASE=".

 

[Pat Hartman]

Yes, you can do this. I even have a code sample because I've done it but NOT for this reason.

I would not go down this road if you put a gun to my head. A better solution is to always keep the flaky locations offline all the time. At the end of business each day, create an extract of the day's transactions. If the connection is available, then run the process that applies the day's transactions and download a fresh copy of the database. When all is well move the extract file to the applied folder so you know it has been processed. To be safe though, you should keep a log and make sure you track what files have been applied and what have not. Log the file name and the date/time applied. Also, make sure you don't skip a day. If you are open 7-days a week, this is easy. If not, it's a little more complicated. The important thing is to not miss a day and not double post so your procedure needs to protect against those two things.

If the internet is not available, you will try again the next day.

The code I use to swap ACE/SQL Server relies on a table of tables with their details. The code deletes the linked tables using this list and then links them to either ACE or SQL Server as requested.

Database    Server    ODBCTableName    LocalTableName    JetTableName    DSN
DEA_Audit    PAT-PC    dbo.tblAuditLog    tblAuditLog    tblAuditLog    DEA_Audit
DEA_Audit    PAT-PC    dbo.tblAuditParms    tblAuditParms    tblAuditParms    DEA_Audit
DEA_Audit    PAT-PC    dbo.tblBookmarks    tblBookmarks    tblBookmarks    DEA_Audit
DEA_Audit    PAT-PC    dbo.tblComments    tblComments    tblComments    DEA_Audit
DEA_Audit    PAT-PC    dbo.tblDependents    tblDependents    tblDependents    DEA_Audit
........................

Private Sub DeleteLinks()
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim rs As DAO.Recordset
    
On Error GoTo Err_Proc

    Set db = CurrentDb()
    Set td = db.TableDefs("tblODBCDataSources")
    Set rs = td.OpenRecordset
    
    Do Until rs.EOF = True
        db.TableDefs.Delete rs!LocalTableName
        rs.MoveNext
    Loop

   rs.Close
    
Exit_Proc:
    Exit Sub
Err_Proc:
    Select Case Err.Number
        Case 3265   'item not found in this collection
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description, vbCritical
            Resume Exit_Proc
    End Select
End Sub

 

[nector]

It appears the second code delete the links in tblODBCDataSources which suggests like thse links are stored in this table called tblODBCDataSources.

The first Code I do not understand what it does, I try figure it out what is going on here

 

[Pat Hartman]

I just posted a picture of a few rows of the data in the table. The table is used to control what gets deleted and what gets linked. Use whatever code you want to link to SQL Server or to ACE. The table is the intermediary. Use the code I posted to delete the current links. You don't swap the links from ODBC to Jet/ACE. You swap from one ODBC server to another or one Jet/ACE database to another. To swap source types, delete the links and link to the "other" source.

 

[bastanu]

Here is a small sample with how I used to do the same thing (almost, as the Access tables were actually local front-end tables instead of linked Access tables, the file was intended to be a single-user portable app to enter contact info in the field and then synchronize with the SQL server when back in the office).
You should be able to easily change it to use an Access back-end instead of local tables, just add a field to the SettingsTable to hold the path to it and use that in the relinking module.
EDIT: forgot to mention that you need to turn the View System objects on as I use a custom system table (usysRemoteTables) to store the list of tables. You do that from File\Options\Current Database\Navigation Options.
Cheers,

 

[nector]

Thank you so much Pat, this will surely help a lot , its true you did understand what I'm looking for.

 

[Pat Hartman]

As I warned earlier, do not try to do a 2-way reconcile of the databases. Do the controlled sync I suggested.

 

[nector]

Sorry I got stuck on the access part again using the DBguy method which is very handy and nice:

The string below works very well for SQL Server.

ODBC;DRIVER=SQL Server; " & _
"SERVER=NECTORPRIME\SQLExpress;DATABASE=Accounting;Trusted_Connection=Yes"

I have tried this one below , I'm getting arror called ISSAM

For Access back end what could be the correct adjusted string?

DATABASE=Accounts;Trusted_Connection=Yes"

 

[sonic8]

For Access back end what could be the correct adjusted string?

You could quickly find out by linking a table manually and then looking at the connection string that was assigned to the table in the process.