Connecting to access backend from access 2013 frontend using VBA

Question

I have 5-6 clients to whom I will be selling my Access based Product. Where there is Back-end Database file (Password Protected). Access accdr Front-end file which saves data in back-end file. The location of the backend file will change client to client, hence want to have a VBA Code which links front-end t back-end.

I tried the below code

sConnect = "Provider=Microsoft.ACE.OLEDB.12.0; " _ 
    & Data Source= " & "C:\MyDB_be.accdb" & ";" _ 
    & "Jet OLEDB:Database Password=123;"

But, the tables are not getting reconnected.

I Got the above code from this Ques on Stackoverflow.

Then I tried the below code

Const LnkDataBase = "C:\MyDB_be.accdb"
Sub relinktables()
'Routine to relink the tables automatically. Change the constant LnkDataBase to the desired one and run the sub
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strTable As String
Set dbs = CurrentDb()
For Each tdf In dbs.TableDefs
    If Len(tdf.Connect) > 1 Then 'Only relink linked tables
        If tdf.Connect <> ";DATABASE=" & LnkDataBase Then 'only relink tables if the are not linked right
            If Left(tdf.Connect, 4) <> "ODBC" Then 'Don't want to relink any ODBC tables
                strTable = tdf.Name
                dbs.TableDefs(strTable).Connect = ";DATABASE=" & LnkDataBase
                dbs.TableDefs(strTable).RefreshLink
            End If
        End If
    End If
Next tdf
End Sub

This works, when the file is not password protected. This code I got from This Ques. But there is no provision of specifying password.

Please help me out.

Either point out mistake in 1st code. OR How to specify password in 2nd Code OR New code altogether.

Spent 4 hrs on searching for solution. New to access VBA.

Went through this and this, but did not understand how to implement.

I tried `dbs.TableDefs(strTable).Connect = "Provider=Microsoft.ACE.OLEDB.12.0; " & "DATABASE= " & LnkDataBase & ";Jet OLEDB:Database Password=123;"` But still not able to get it working. This code is giving error. — Adarsh Madrecha, Aug 06 '15 at 10:02

score 1 · Answer 1 · answered Aug 05 '15 at 23:52

1

Give this a try:

Const LnkDataBase = "C:\MyDB_be.accdb"
Sub relinktables()
'Routine to relink the tables automatically. Change the constant LnkDataBase to the desired one and run the sub
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strTable As String
Set dbs = CurrentDb()
For Each tdf In dbs.TableDefs
    If Len(tdf.Connect) > 1 Then 'Only relink linked tables
        If tdf.Connect <> ";DATABASE=" & LnkDataBase Then 'only relink tables if the are not linked right
            If Left(tdf.Connect, 4) <> "ODBC" Then 'Don't want to relink any ODBC tables
                strTable = tdf.Name
                dbs.TableDefs(strTable).Connect = "Provider=Microsoft.ACE.OLEDB.12.0; " _ 
& "Data Source= " & LnkDataBase & ";" _ 
& "Jet OLEDB:Database Password=123;"
                dbs.TableDefs(strTable).RefreshLink
            End If
        End If
    End If
Next tdf
End Sub

answered Aug 05 '15 at 23:52

JCro

686
5
10

users will be running front-end on Access 2013, so Microsoft.ACE.OLEDB.12.0 will work? OR I should use Microsoft.ACE.OLEDB.15.0 if this is logical.? – Adarsh Madrecha Aug 06 '15 at 00:13
12 will work, but 15 should also work. Test with 15, if successful, use 15. – JCro Aug 06 '15 at 01:51
If the location of file is not accessible (Due to PC not connected to Network). The above code crashes Access. Can we add Some timeout to above code? – Adarsh Madrecha Aug 06 '15 at 09:05
Tried your solution, it gives error `Runtime error 3170`. `Could Not find installable ISAM` – Adarsh Madrecha Aug 06 '15 at 09:55

score 0 · Accepted Answer · answered Aug 06 '15 at 10:23

After investing another 4 Hrs to this problem. Finally found the solution.

This is the code which worked flawlessly.

Const LnkDataBase = "C:\MyDB_be.accdb"
Const DBPassword = "123"

Sub relinktables()
'Routine to relink the tables automatically. Change the constant LnkDataBase to the desired one and run the sub
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strTable As String
Set dbs = CurrentDb()
For Each tdf In dbs.TableDefs
    If Len(tdf.Connect) > 1 Then 'Only relink linked tables
        If tdf.Connect <> ";DATABASE=" & LnkDataBase Then 'only relink tables if the are not linked right
            If Left(tdf.Connect, 4) <> "ODBC" Then 'Don't want to relink any ODBC tables
                strTable = tdf.Name
                dbs.TableDefs(strTable).Connect = "MS Access;PWD=" & DBPassword & ";DATABASE=" & LnkDataBase
                dbs.TableDefs(strTable).RefreshLink
            End If
        End If
    End If
Next tdf
End Sub

I would encourage VBA Experts to add their comments OR Modify this code to add Error Debugging. Like - IF PC is not connected to network, and the path specified is on Network, then Access hangs. This issue is yet to be fixed.

What is the error code? You could just add `ON ERROR GOTO err_hndl` and deal with it there. Otherwise you can test for network connectivity using something like `If (Len(Dir("MappedDriveLetterHere:\"))) Then blnConnected=True` — JCro, Aug 06 '15 at 22:44
There is NO Error shown. The Access Run time just Hangs. Nothing happens, and when Close button is pressed, Access Stops working and closes. — Adarsh Madrecha, Aug 07 '15 at 12:34

Connecting to access backend from access 2013 frontend using VBA

2 Answers2

Linked

Related