Hard code a back-end server path in a front-end?

[Harmannus]

Hallo,

Is this going to become the longest thread ever ;-)

I get a runtime error 3219/invalid operation/debug:tdf.Connect =
";DATABASE=k:\databases\mydatabase_be.mdb"

Hopes this helpes to solve *my* problem.

Regards,
Harmannus

 

[Douglas J. Steele]

Your code is going to set the Connect property for all TableDef objects in
the TableDefs collection. That means for the System tables as well. You
definitely don't want that.

If your tables are already linked, and you're just trying to change them,
try

For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=k:\databases\mydatabase_be.mdb"
tdf.RefreshLink
End If
Next

Alternatively, you can exclude the system tables using:

For Each tdf In CurrentDb.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
tdf.Connect = ";DATABASE=k:\databases\mydatabase_be.mdb"
tdf.RefreshLink
End If
Next

 

[Douglas J. Steele]

I just went back and rechecked Dev's code in
http://www.mvps.org/access/tables/tbl0009.htm That's simply for Access
frontends connecting to Jet backends, and he doesn't delete the existing
TableDef objects there. As well, I went and tested with an Access frontend
to Jet backend, and it works fine.

Dev does, however, delete them and recreate them in
http://www.mvps.org/access/tables/tbl0010.htm which is for ODBC connection,
and that would corresponds to the DSN-less situation.

 

[Harmannus]

Hallo,

Thanx for the reply!

Now i get a message "not a valid path" error 3044

I do not have access to the server on which the back-end resides! I changed
the path to me local back-end for develop reasons and *simply* want to
change it back to its orginal state.

Is it possible to skip the check for a valid path?

Regards,
Harmannus

My code (based on your suggestion):

Private Sub cmdUpdate_Click()
Dim db As Database
Dim tdf As DAO.TableDef

For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=k:\databases\mydatabase_be.mdb"
tdf.RefreshLink
End If
Next

End Sub



Now i get a message n

 

[Harmannus]

And the second code suggestion says object missing. Debug stops at
tdfCurr/error 424

Regards,
Harmannus

 

[Douglas J. Steele]

No, you need access to the file, or you won't be able to change the path.

Put the code in to relink it for people who do have access.

 

[Douglas J. Steele]

Sorry: my fault. That's the trouble with cutting and pasting from different
sources.

For Each tdf In CurrentDb.TableDefs
If (tdf.Attributes And dbSystemObject) = 0 Then
tdf.Connect = ";DATABASE=k:\databases\mydatabase_be.mdb"
tdf.RefreshLink
End If
Next

Of course, as I told you in response to your other post, it isn't going to
work if you don't have access to the backend.

 

[Albert D. Kallal]

Hallo,

Is it possible to hard code a back-end *server* path, that i do not have
access to, into a front-end?

For develop reasons i use a local back-end to make changes in the front-end
and sent it back. For this purpose i want to reset the path in the front-end
to its orginal state. The code below checks for a valid connection as far as
i do understand the code. This check should not be made. It should simply
update the linked tables to the original path.

No, you cannot link to a database if you don't have access to it. The
solution is to simply check at start-up, and then re-link it. It is a one
time affair, and should not be a problem. In fact, I have a actually have a
table in the front end with one record, and two fields for the back end. One
field is for path to back end (production), and path to back end
development. Right before I send out the mde, I simply check mark the flag
for production back end, and then the user at start-up is informed about
this..and my code re-links.

So, no..you can't set the location of the back end BEFORE you deploy, but
you can certainly save the location of WHERE you want to link to...and then
simply link at start-up.

The end result is the same thing....

 

[Rick Brandt]

You tried the connect strings Carl Prothman has at
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForAS400FromIBM
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForAS400AndVSAM
(and followed the links for more information)?

Well I tried the one for ODBC and it doesn't work for me. The OleDB method of
creating a connection might work, but I can't link a table with that (can I?).

 

[Douglas J. Steele]

http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForAS400FromIBM
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForAS400AndVSAM

Well I tried the one for ODBC and it doesn't work for me. The OleDB method of
creating a connection might work, but I can't link a table with that (can

I?).

D'oh! I didn't pay close enough attention, did I?

No, I haven't found any way to create linked tables using OleDb.