"Run-time error - 3219 Invalid Operation (1 Viewer)

[Ashfaque]

Hi,

I am relinking Access tbls from BE to FE both are Ms Access db but while executing below code , I am receiving "Run-time error - 3219" on line
"tdf.Connect = strConnect" in below tbl Relinking code.

Dim strDbFile As String
Dim strPassword As String
Dim strConnect As String

    strDbFile = "D:\Ashfaque\GREC_BE.accdb"
    strPassword = "0631995"
    strConnect = "[MS Access];PWD=" & strPassword & ";DATABASE=" & strDbFile

    Dim tdf As DAO.TableDef
    Dim db As DAO.Database

    Set db = CurrentDb
    For Each tdf In db.TableDefs
        ' ignore system and temp tables
        If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*" Or tdf.Name Like "exl*") Then
            tdf.Connect = strConnect
            tdf.RefreshLink
        End If
    Next

Can anyone help me please...

 

[Gasman]

I have google on this laptop.

Connecting to access backend from access 2013 frontend using VBA

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

stackoverflow.com

I take it you have not walked through the code to see what table you are getting the error on?

vba error 3219 linking error - Google Search

 

[Ashfaque]

Thanks,

I have NO tbl in FE and trying to link all tbls from BE to FE.. I tried even your code in given link but not linking.

 

[Gasman]

Not my code, just found via a quick google search? that said it worked?
I would walk though the code and see what table causes the error. Inspect and test variables in the immediate window.
There are also samples here on this site re linking to BE, not sure if they include the password though.

 

[Ashfaque]

Also below code connecting NO tables. No error reporting too.

Option Compare Database
Option Explicit
Const LnkDataBase = "D:\Ashfaque\GREC_BE.accdb"
Const DBPassword = "06031995"
Sub RefreshLinks()
'
'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
 MsgBox "Linked All Table From BE"
End Sub

 

[Ashfaque]

Immediate window shows:

[MS Access];PWD=06031995;DATABASE=D:\Ashfaque\GREC_BE.accdb
[MS Access];PWD=06031995;DATABASE=D:\Ashfaque\GREC_BE.accdb
[MS Access];PWD=06031995;DATABASE=D:\Ashfaque\GREC_BE.accdb
[MS Access];PWD=06031995;DATABASE=D:\Ashfaque\GREC_BE.accdb
[MS Access];PWD=06031995;DATABASE=D:\Ashfaque\GREC_BE.accdb
[MS Access];PWD=06031995;DATABASE=D:\Ashfaque\GREC_BE.accdb

but noting connecting

 

[Gasman]

Have you tried without a password? Remove that from the BE.
Should pw be in quotes, even if numeric?

 

[CJ_London]

you are clearly not walking through the code a line at a time. And your code does not include the debug line so no idea if that is relevant or not.

I can see something which does not look right - easily seen if you walk through the code.

 

[Ashfaque]

Have you tried without a password? Remove that from the BE.
Should pw be in quotes, even if numeric?

Yes I tried removing password of BE and executed the code keeping PWD="" in code....nothing happened..

 

[Gasman]

I have gone as far as I can go, as I have never had to do it via vba, and I am in a hospital car park on my phone ATM. I do not have the inclination to try on any of my dbs, even if I were at home.

 

[Ashfaque]

Strange...

Even I remove all the code simply using following code to link the table it provides Run-time error "3265"..Item not found in this collection...

CurrentDb.TableDefs("T_BankAccDetails").Connect = _
"MS Access;PWD=06031995;DATABASE=D:\Ashfaque\GREC_BE.accdb"

 

[Gasman]

See if the code here is of any help.

How to Link Access Database Front End to Back End Tables at Startup

How, at start-up of front end of a split Access database, to use VBA code to link to back-end tables

www.consultdmw.com

 

[CJ_London]

Run-time error "3265"..Item not found in this collection...

that is a different error.


consider the logic of this code

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

you comment 'Don't want to relink any ODBC tables

then that is exactly what you try to do - but to an access db

@Ashfaque - when you step through the code - what line does the error occur on?

 

[Ashfaque]

@Ashfaque - when you step through the code - what line does the error occur on?

Doesn't show any error...it is passing from each line to downwords and finally msg appearing "Linked All Table From BE"

but in fact nothing relinking..

 

[Gasman]

that is a different error.


consider the logic of this code

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

you comment 'Don't want to relink any ODBC tables

then that is exactly what you try to do - but to an access db

@Ashfaque - when you step through the code - what line does the error occur on?

Yes, that is from the code I linked to, but it appears to have been amended by the O/P?
That link said it worked fine, but without the provision of a password.

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

@Ashfaque why not link the tables manually, then inspect the connection string and see what that actually is?
Inspecting that code, it is changing the BE, but tables are ALREADY linked.?
If your tables are not currently linked, then the bulk of that code is not going to execute.

You have to take time to understand the code Amending <> to = gives the impression that you do not understand that code?

 

[Ashfaque]

@Ashfaque why not link the tables manually, then inspect the connection string and see what that actually is?

If connected all tables manually, there is no prob at all. It is refreshing thru this code.
What I was thinking in case if the BE place is NOT changes and from FE we deletes all linked tbls then while opening FE it should automatically Re-Link all tables from BE into FE.

Anyways, thanks a lot for your time and efforts.

With kind regards,

 

[gemma-the-husky]

@Gasman has it correct in #15

dbs.TableDefs(strTable).Connect = ";DATABASE=" & LnkDataBase

Just because the immediate window shows something different doesn't mean that is how you form the connection string.

Try this to show the actual connection string. Note that this doesn't show the connected table itself, which is part of the tabledef object. The connection string just shows the connected database, and it looks like @Gasman example

Sub showconnect()
Dim tdf As TableDef
    MsgBox CurrentDb.TableDefs("a connected table name").Connect
End Sub

 

[CJ_London]

the connect properties for a password protected access BE is
MS Access;PWD=password;DATABASE=pathandfilename

the connect properties for a non protected access BE is
;DATABASE=pathandfilename

And for a non linked table the connection property exists and is populated with zls

So your code should work although it will relink a table that is already linked correctly - I'm guessing there is something wrong with your setup. A wrong password, path or filename would generate an error on refreshlink - but then you have never told us exactly where you get your error.

Perhaps the current db is not updateable for some reason - maybe other users have it open, maybe you have a second copy open somewhere which means you do not have exclusive use to be able to make that change. Perhaps one of the tables you are trying to change is actually open. Perhaps you have a small corruption. Perhaps the required table does not exist in the new BE

Suggest restart your computer, compact/repair your current db and also decompile/recompile. See if that solves the problem

 

[gemma-the-husky]

@Ashfaque
maybe the problem is your [] square brackets shouldn't be included.

 

[Ashfaque]

Thanks CJ, gemma-the-husky, Gasman..

Problem still persist

1. My both FE and BE and in same drive "D:\Ashfaque" & BE is Access db
2. Thinking that PWD BE may not be suitable so I removed password of my BE
3. Only single user is using at the moment. No second copy has been made or in use.
4. Restarted pc 2-3 times.

as per the post 15 of Gasman, it should re-link the tbls of BE into FE.

What I wanted to do is; remove all linked tbl in FE and once the db starts up, it should RE-LINK all the tbls from BE.