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
– Adarsh Madrecha, Commented Aug 6, 2015 at 10:02

JCro · Accepted Answer · 2015-08-05 23:52:51Z

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 5, 2015 at 23:52

JCro

6965 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Adarsh Madrecha Over a year ago

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

JCro Over a year ago

12 will work, but 15 should also work. Test with 15, if successful, use 15.

Adarsh Madrecha Over a year ago

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 Over a year ago

Tried your solution, it gives error Runtime error 3170. Could Not find installable ISAM

Adarsh Madrecha · Accepted Answer · 2015-08-06 10:23:44Z

0

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.

answered Aug 6, 2015 at 10:23

Adarsh Madrecha

8,20514 gold badges78 silver badges129 bronze badges

2 Comments

JCro Over a year ago

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

Adarsh Madrecha Over a year ago

There is NO Error shown. The Access Run time just Hangs. Nothing happens, and when Close button is pressed, Access Stops working and closes.

Collectives™ on Stack Overflow

Connecting to access backend from access 2013 frontend using VBA

2 Answers 2

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related