I have a puzzle here to connect to either SQL Server or Access Database

[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=".

 

[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

 

[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.

 

[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.