Linking Problem

[John]

Dear All,
I am still rusty at this. I have a csv file that is linked into an Access
table. I need to be able to point the link to another location but keep the
same field mappings. Any help would be appreciated.

Thanks.
John.

 

[Van T. Dinh]

If you meant you cannot specify the new path for the CSV file in the Linked
Table Manager, there is a small CheckBox with label something like "Always
ask for full path to linked Table".

If you select the Linked Table with the CSV as the Source file, check this
CheckBox and OK, Access will ask you for the new full path-name.

 

[John]

Van,
I appreciate the response. I am looking to do this via VB code against an
Access DB. I need to change the Path of the file. I can change the file
name but the path does not change.

Thanks.
John.

 

[Van T. Dinh]

Check Access VB Help on the TransferText Method.

My guess is that you need to delete the existing Linked Table and create a
new one with the new full pathname. Check also on the Import/Export
Specification that can be used with the TransferText Method.

 

[Ron Hinds]

Van,
I appreciate the response. I am looking to do this via VB code against an
Access DB. I need to change the Path of the file. I can change the file
name but the path does not change.

What you want to do is Relink the Table in code. The Path to the .CSV is
part of the TableDef's Connect string. To relink:

CurrentDb.TableDefs("your_table_name").Connect = "new_connect_string"
CurrentDb.TableDefs("your_table_name").RefreshLink

To see what the "new_connect_string" should look like, in the Debug window
type:

?CurrentDb.TableDefs("your_table_name").Connect

and press Enter.

 

[Van T. Dinh]

I just did some quick tests and you should be able to change the Connect
String of the Linked Table.

Quick & dirty code from A2K2 Immediate windows (make sure you add DAO
Library to the References):

' Existing csv file in A2K2Sample_Test
?DBEngine.WorkSpaces(0)(0).TableDefs("Categories").Connect
Text;DSN=Categories Link Specification;FMT=Delimited;HDR=NO;
IMEX=2;CharacterSet=437;DATABASE=D:\AccessTests\A2K2Sample_Test

' Link to a different csv file same name in A2K2_MyTest
DBEngine.WorkSpaces(0)(0).TableDefs("Categories").Connect = _
"Text;DSN=Categories Link Specification;FMT=Delimited;HDR=NO;
IMEX=2;CharacterSet=437;DATABASE=D:\AccessTests\A2K2_MyTest"

' Check linking
?DBEngine.WorkSpaces(0)(0).TableDefs("Categories").Connect
Text;DSN=Categories Link Specification;FMT=Delimited;HDR=NO;
IMEX=2;CharacterSet=437;DATABASE=D:\AccessTests\A2K2_MyTest

' Refresh link so that Access shows changed Linked Table
' (since we use DAO code and Access still has the old link)
DBEngine.WorkSpaces(0)(0).TableDefs("Categories").RefreshLink

After this, I opened the Linked Table and clearly Access is showing the data
in the A2K2_MyTest dir.

 

[John]

Van,
Thanks for the DAO code. It gives me a start. However, My application is
using ADO through a VB project to access the Access DB. Is there an ADO
example to do this?

Also. Using DAO, the Connect String
reads:";DATABASE=\\Ntserv0002\Projects\STS\STSDLL\STSData.mdb"

John

 

[Van T. Dinh]

Sorry, I don't use the ADO code often. Perhaps, someone else can jump in
with the ADO code.

Didn't you say the Source was a CSV file? (see your original post).

Regardless, try setting the Connect Property to:

";DATABASE=NEW_PATH\NEW_STSData.mdb"

 

[Ron Hinds]

Van,
Thanks for the DAO code. It gives me a start. However, My application is
using ADO through a VB project to access the Access DB. Is there an ADO
example to do this?

Also. Using DAO, the Connect String
reads:";DATABASE=\\Ntserv0002\Projects\STS\STSDLL\STSData.mdb"

John

John - there is no way through ADO to change the Connect string for a Linked
Access table. ADO doesn't understand the concept of linked tables. Why can't
you use DAO in your code just for this one purpose? It *is* possible to use
both ADO *and* DAO in the same VB project. The only caveat is that you need
to dereference any objects you use that have the same name in both ADO and
DAO, e.g., Recordset:

Dim rs As ADODB.Recordset 'rs is an ADO Recordset object

- or -

Dim rs As DAO.Recordset 'rs is a DAO Recordset object

 

[sajid_mansoori]

Hi Guys,

I'm also have the same problem as John posted earlier. The only thin
different here is that I'm using a DSN instead of a file. My DSN, name
'MAS', was connected to the test server. I want to refresh the links s
that the linked tables now points to the production server (I modif
the DSN configuration of 'MAS' to point it to production server an
then run the code with the following .Connect string)

..Connect = "ODBC;Database=MonitorDB;DSN=MAS;Integrated Security=True"

PS : Before running the above code, I'm deleting the old linked table
and recreating using the above connect statement and RefreshLink code.

The problem is that after running the code, the data in the table stil
shows the data for TESTSERVER. If i close my Access application an
reopen again, the table shows the data for the new Server.

Please help

Sajid

I just did some quick tests and you should be able to change th
Connect
String of the Linked Table.

Quick & dirty code from A2K2 Immediate windows (make sure you ad
DAO
Library to the References):

' Existing csv file in A2K2Sample_Test
?DBEngine.WorkSpaces(0)(0).TableDefs("Categories").Connect
Text;DSN=Categories Link Specification;FMT=Delimited;HDR=NO;
IMEX=2;CharacterSet=437;DATABASE=D:\AccessTests\A2K2Sample_Test

' Link to a different csv file same name in A2K2_MyTest
DBEngine.WorkSpaces(0)(0).TableDefs("Categories").Connect = _
"Text;DSN=Categories Link Specification;FMT=Delimited;HDR=NO;
IMEX=2;CharacterSet=437;DATABASE=D:\AccessTests\A2K2_MyTest"

' Check linking
?DBEngine.WorkSpaces(0)(0).TableDefs("Categories").Connect
Text;DSN=Categories Link Specification;FMT=Delimited;HDR=NO;
IMEX=2;CharacterSet=437;DATABASE=D:\AccessTests\A2K2_MyTest

' Refresh link so that Access shows changed Linked Table
' (since we use DAO code and Access still has the old link)
DBEngine.WorkSpaces(0)(0).TableDefs("Categories").RefreshLink

After this, I opened the Linked Table and clearly Access is showin
the data
in the A2K2_MyTest dir.

-
sajid_mansoor

 

[Y2Chris75]

I have some code that is very similar to what is being discussed in thi
thread and I have a problem that I have been unable to resolve with i
for several months now (a number of message board postings have turne
up nothing)....

I do not want to grant Table access to my users because they can scre
up other things, so I crafted up this button that would allow them t
relink a specific table to a new TXT file periodically. It deletes th
existing linked table, then allows them to browse out and choose a ne
file, then I use code similar to the examples above to relink to th
new file name (using my already setup Linked file specification calle
"WOUpdate Link Specification2").

It all works great! UNTIL ... I close the database and reopen. The
if I try to run the code, it tells me it can't find the linked fil
specification "WOUpdate Link Specification2" ... but if I manuall
relink, the file spec is there! As a matter of fact, if I jus
manually go as far as bringing up the "Link Text Wizard" and the
cancel out, I can go run my code. I just can't close the database an
have it work again unless I go at least that far first.

What do I need to do to initialize those file specifications so my cod
can run (without putting my user through that step --- I want to kee
them out of the tables or I would just have them using the Linked Tabl
Wizard and not have a need for this code)

If pasting the code will help, let me know


-
Y2Chris7