Thanks.
John.
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...
Thanks.
John.
"Van T. Dinh" <VanT...@discussions.microsoft.com> wrote in message
news:O123JW82...@TK2MSFTNGP12.phx.gbl...
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...
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.
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...
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...
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...
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
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
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