Linking Problem
John
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
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.
--
HTH
Van T. Dinh
MVP (Access)
"John" <cpsi....@insight.rr.com> wrote in message
news:VSBNb.2043$Gs2....@fe1.columbus.rr.com...
John
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" <VanT...@discussions.microsoft.com> wrote in message
news:O123JW82...@TK2MSFTNGP12.phx.gbl...
Van T. Dinh
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.
--
HTH
Van T. Dinh
MVP (Access)
"John" <cpsi....@insight.rr.com> wrote in message
news:WdWNb.961$DE....@fe2.columbus.rr.com...
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
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.
--
HTH
Van T. Dinh
MVP (Access)
"John" <cpsi....@insight.rr.com> wrote in message
news:WdWNb.961$DE....@fe2.columbus.rr.com...
John
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" <VanT...@discussions.microsoft.com> wrote in message
news:%23zey00H...@TK2MSFTNGP10.phx.gbl...
Van T. Dinh
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"
--
HTH
Van T. Dinh
MVP (Access)
"John" <cpsi....@insight.rr.com> wrote in message
news:06TOb.26957$f97...@fe3.columbus.rr.com...
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 thing
different here is that I'm using a DSN instead of a file. My DSN, named
'MAS', was connected to the test server. I want to refresh the links so
that the linked tables now points to the production server (I modify
the DSN configuration of 'MAS' to point it to production server and
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 tables
and recreating using the above connect statement and RefreshLink code.
The problem is that after running the code, the data in the table still
shows the data for TESTSERVER. If i close my Access application and
reopen again, the table shows the data for the new Server.
Please help
Sajid
Van T. Dinh wrote:
> [B]I just did some quick tests and you should be able to change the
--
sajid_mansoori
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message288037.html
Y2Chris75
I have some code that is very similar to what is being discussed in this
thread and I have a problem that I have been unable to resolve with it
for several months now (a number of message board postings have turned
up nothing)....
I do not want to grant Table access to my users because they can screw
up other things, so I crafted up this button that would allow them to
relink a specific table to a new TXT file periodically. It deletes the
existing linked table, then allows them to browse out and choose a new
file, then I use code similar to the examples above to relink to the
new file name (using my already setup Linked file specification called
"WOUpdate Link Specification2").
It all works great! UNTIL ... I close the database and reopen. Then
if I try to run the code, it tells me it can't find the linked file
specification "WOUpdate Link Specification2" ... but if I manually
relink, the file spec is there! As a matter of fact, if I just
manually go as far as bringing up the "Link Text Wizard" and then
cancel out, I can go run my code. I just can't close the database and
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 code
can run (without putting my user through that step --- I want to keep
them out of the tables or I would just have them using the Linked Table
Wizard and not have a need for this code)
If pasting the code will help, let me know.
--
Y2Chris75