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

[Harmannus]

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.

http://www.mvps.org/access/tables/tbl0009.htm

I got a suggestion earlier to change te part where the code asks for the
user the state the parth with: strNewPath = "k:\databases\mydatabase_be.mdb"

Thanx in advance for any tips to solve this problem.

Regards,
Harmannus

 

[Scott McDaniel]

Do this;

Dim tdf As DAO.TableDef

For each tdf in currentdb.tabledefs
tdf.connect = yourconnectstring
tdf.RefreshLink
Next

Note that "yourconnectstring" must be in a particular format:

";DATABASE=C:\YourFolder\Yourdatabase.mdb;TABLE=tblTable1"

 

[Rick Brandt]

Do this;

Dim tdf As DAO.TableDef

For each tdf in currentdb.tabledefs
tdf.connect = yourconnectstring
tdf.RefreshLink
Next

Note that "yourconnectstring" must be in a particular format:

";DATABASE=C:\YourFolder\Yourdatabase.mdb;TABLE=tblTable1"

Actually I was just working with this same task and found that you can do the
above with QueryDefs, but not TableDefs. The Help file indicates that you can
only manipulate the connect property on a TableDef before it has been appended
to the TableDefs collection (i.e when you are creating it). After that the
property is read only. You have to delete and then recreate the link using the
new connect string.

 

[Scott McDaniel]

Hi Rick,

I've successfully used this code to relink tables without deleting the
tabledef first:

Do Until rstTables.EOF
Set tdf = dbs.TableDefs(rstTables("strTable"))

If Len(strPath) > 0 Then
tdf.Connect = ";DATABASE=" & strPath & ";TABLE=" & tdf.Name
Else
tdf.Connect = rstTables("strLink")
End If 'If Len(BackEndPath) > 0

tdf.RefreshLink

rstTables.MoveNext
Loop

Perhaps I misunderstood your comment ...
--
Scott McDaniel
CS Computer Software
Visual Basic - Access - Sql Server - ASP

"Smash forehead on keyboard to continue ... "

 

[Rick Brandt]

Are you refreshing existing links or changing them? I ran a routine similar to
yours where I was attempting to change the connect string to point to a
different DSN and supply UserName and Password information. The connect
properties of all of the Pass-Through queries were properly modified while the
connect properties of the TableDefs were unaffected. It was at that point that
I looked in the Help file and saw the following....

*********
For Database objects, new Connection objects, linked tables, and TableDef
objects not yet appended to a collection, this property setting is read/write.
*********

Perhaps I am misreading it, but doesn't the above say that you can only modify
the connect property of a TableDef _before_ it is appended to the TableDefs
collection?

When I stepped through my code, the connect string for tables appeared to
change. If I examine it immediately before and then immediately after setting
it, it is different. However if I run the procedure again the "before" setting
has reverted back to the original one. The change doesn't seem to make it past
the end of the routine.

 

[Douglas J. Steele]

It's my understanding as well, Rick, that you can't change the Connect
property once the TableDef object has been added to the TableDefs
collection.

Certainly, that's why I delete all of the TableDef objects in my DSN-Less
connection example at
http://members.rogers.com/douglas.j.steele/DSNLessLinks.html

 

[Scott McDaniel]

Hi Rick,

These are existing tabledefs, and they may or may not be changed depending
on what the user requests. This app is a frontend for several different
projects, all of which use identical table structures ... the tables are
kept on different servers (company policies, etc require this) ... I created
a small VB project that writes new path information to a registry key, and
the Access project retrieves that value upon startup and then either (a)
updates the links already exist or (b) builds the tabledef with the connect
info if it doesnt. This code builds new tabledefs:


Do Until rstTables.EOF
Set tdf = New DAO.TableDef

With tdf
.Connect = ";DATABASE=" & strPath
.SourceTableName = rstTables("strTable")
.Name = rstTables("strTable")
CurrentDb.TableDefs.Append tdf
End With

rstTables.MoveNext

If blnOK Then Forms!frmLink.UpdateBar = nCount + 1
nCount = nCount + 1
Loop

The excerpt from the help file is confusing, to say the least. It certainly
reads like you cannot change the connect string except for new tabledefs ...
but there's also a reference to linked tables, which may explain the
behaviour.

RE: The change doesn't seem to make it past the end of the routine ... did
you include the RefreshLinks after changing the connect string

--
Scott McDaniel
CS Computer Software
Visual Basic - Access - Sql Server - ASP

"Smash forehead on keyboard to continue ... "

 

[Jim Allensworth]

I reconnect existing tabledefs routinely. For testing I connect to a
local copy of the backend. When done I connect back to the production
backend.

As mentioned by Scott McDaniel you need to do a Refresh of the the
TableDefs for the connection to stick.

Also, as an aside, deleting and recreating tabledefs will bloat the
front end.

For example, if you are creating/connecting to temporary databases to
hold temporary data (instead of temp tables) AND you delete and
recreate them then you will experience bloat.

The best way to avoid the bloat is to re-connect the table instead of
deleting/recreating it. (of course this is relevant only in a
production FE)

- Jim

 

[Rick Brandt]

It's my understanding as well, Rick, that you can't change the Connect
property once the TableDef object has been added to the TableDefs
collection.

Certainly, that's why I delete all of the TableDef objects in my DSN-Less
connection example at
http://members.rogers.com/douglas.j.steele/DSNLessLinks.html

I actually tried to use your code example, but it apparently doesn't work with
all back ends. I am using the IBM Client Access driver to connect to an ISeries
(AS400) server and it absolutely will not work without a User or System DSN. It
won't work totally DSNless and it won't work with a File DSN.

It's a shame really because this driver has a lot more optional settings that
*must* be set a certain way for everything to work and it's been a real PITA to
make sure that all users have them set up correctly. A DSNLess solution would
be ideal here, but I just couldn't get it to work.

 

[Rick Brandt]

I reconnect existing tabledefs routinely. For testing I connect to a
local copy of the backend. When done I connect back to the production
backend.

As mentioned by Scott McDaniel you need to do a Refresh of the the
TableDefs for the connection to stick.

Also, as an aside, deleting and recreating tabledefs will bloat the
front end.

For example, if you are creating/connecting to temporary databases to
hold temporary data (instead of temp tables) AND you delete and
recreate them then you will experience bloat.

The best way to avoid the bloat is to re-connect the table instead of
deleting/recreating it. (of course this is relevant only in a
production FE)

Mine are ODBC linked tables though, not linked Jet tables. Does that make a
difference in whether the connection can be changed? I'm pretty sure I tried
the RefreshLink, but I'll look at it again.

 

[Harmannus]

Hallo,

Thanx for all the replies ;-) Still trying to understand what all the
suggestions mean...

I tried your suggestion:

Private Sub cmdUpdate_Click()
Dim tdf As DAO.TableDef
For Each tdf In CurrentDb.TableDefs
tdf.Connect = ";DATABASE=k:\databases\mydatabase_be.mdb"
tdf.RefreshLink
Next
End Sub

But i get a error on the tdf.connetct line...

I am missing something? This should update all my linked tables? I also have
some tables that are not linked. Those are not effect by the above code (if
it would work)?

Regards,
Harmannus

 

[Jim Allensworth]

Mine are ODBC linked tables though, not linked Jet tables. Does that make a
difference in whether the connection can be changed? I'm pretty sure I tried
the RefreshLink, but I'll look at it again.

I believe you are correct that RefreshLink is for Jet connections
only.

- Jim

 

[Jim Allensworth]

You might set CurrentDb to a variable. It may be going out of scope in
the next line of code.

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

Set db = CurrentDb
For Each tdf In db.TableDefs
tdf.Connect = ";DATABASE=k:\databases\mydatabase_be.mdb"
tdf.RefreshLink
Next
db.TableDefs.Refresh
Set db = Nothing
End Sub

Also, Refresh your tabledefs collection.

- Jim

 

[Harmannus]

Hallo,

Thanx for the reply and code!

What do you mean with Refresh your tabledefs? How do i do this?

Regards,
Harmannus

 

[Harmannus]

Hallo,

Tried your code. Still get a error on the tdf.connect line....
Any other suggestions as to what is wrong?

Regards,

Harmannus

 

[Jim Allensworth]

Are your References OK? Check to make sure that you have DAO checked
and if so it's not broken.

- Jim

 

[Harmannus]

Hallo,

Checked the reference! Its ok!

Any other suggestions or do i have to refert to the dos command subst...

Regards,

Harmannus

 

[Scott McDaniel]

What error are you getting?

--
Scott McDaniel
CS Computer Software
Visual Basic - Access - Sql Server - ASP

"Smash forehead on keyboard to continue ... "

 

[Scott McDaniel]

Hi Doug and Rick,

Since this email exchange, I've gone back and checked ...

My app(s) are strictly Access frontends connecting to Jet backends. I have
existing Tabledefs, and I iterate through them like this:

Do Until rstTables.EOF
Set tdf = dbs.TableDefs(rstTables("strTable"))

If Len(strPath) > 0 Then
tdf.Connect = ";DATABASE=" & strPath & ";TABLE=" & tdf.Name
Else
tdf.Connect = rstTables("strLink")
End If 'If Len(BackEndPath) > 0

tdf.RefreshLink

rstTables.MoveNext
Loop

rstTables opens a table that contains the names of all linked tables ...
these tabledefs are already existing, and when this code bit finishes, the
links are most definitely changed. I've got this identical code in use at
well over 150 workstations which have been running my app for about 2 years
(and I relink every time the frontend opens, simply because the users often
relink to different backend datasets) ... so far no troubles.

I'm wondering now if perhaps I'll run into trouble by not following your and
Rick's advice (re: deleting and re-creating the tabledefs when needed)?
--
Scott McDaniel
CS Computer Software
Visual Basic - Access - Sql Server - ASP

"Smash forehead on keyboard to continue ... "

 

[Douglas J. Steele]

You tried the connect strings Carl Prothman has at
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForAS400FromIBM
and
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForAS400AndVSAM

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I actually tried to use your code example, but it apparently doesn't work with
all back ends. I am using the IBM Client Access driver to connect to an ISeries
(AS400) server and it absolutely will not work without a User or System DSN. It
won't work totally DSNless and it won't work with a File DSN.

It's a shame really because this driver has a lot more optional settings that
*must* be set a certain way for everything to work and it's been a real PITA to
make sure that all users have them set up correctly. A DSNLess solution would
be ideal here, but I just couldn't get it to work.

Afraid I have no experience at all with AS400.

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