VBScript and Access

[Roelf]

I have made a bunch of VBScript which import data from a text file into an Access database. After that import, a set of sql files is executed against the access database and the results are stored in an excel file. This all works on a machine with no access installed. It uses oledb drivers to access the database, excel sheet and the text files.

Problem is, the import takes very long with large text files. Access has the ability to link a table to a text file. But this is a absolute reference to the file. Because i have no control about the location where the users of this software will put it, the absolute location of the file is a problem.

What i need is a way to programmatically change the reference into the correct location of the linked file. Problem is that i cannot use Access to do that, because in most cases, users will not have access installed. It has to happen from VBScript. I have tried to do it using ADOX component, but with no luck.

Anyone done this before and wants to share the code?

pseudocode for what i need:
for each table in database
if table is linked text file then
change link location from dir1 to dir2
end if
next

[Nilpo]

Use the TransferText method. There are several options available so see the link below.DoCmd.TransferText(acLinkDelim,, "Table Name", "datafile.txt", blnHasFieldNames)http://msdn2.microsoft.com/en-us/library/aa220768(office.11).aspx

[Roelf]

Thanks for your answer, but this is only available from within access. I need to change the linked tables in the MDB file from outside (using VBScript) on a machine where Access is not installed.

[Dunna]

You live in the Netherlands. What makes you the luckiest man in the world? ;) (Sorry, don't have an answer to your question)