Need Help in creating a link table in MS-Access dynamically using VB code

[sai.baranidhara]

Hi,
I am using an excel sheet containing around 200 records and linking it to my MS-Access database through the Link Table option available in MS-Access.

But I need to change it everytime when I go from one system to another as the path of the Excel file changes. That is, I need to delete the link table and recreate it, everytime I change my system.

Is there a way to create a link table from my VB code so that the link table can be created in MS-Access database from the application path dynamically? The Excel file, I am trying to link is in the same folder of my VB Project.

Please help me.

Thanks .

BARANIDHARAN

[techtix]

Write a file based DSN that points to your file and then use it in the connection string.

[Example DSN]

[ODBC]
DRIVER=Driver do Microsoft Excel(*.xls)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
ReadOnly=1
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=excel 8.0
DriverId=790
DefaultDir={place app path here}
DBQ={place app path here}\Document.xls

Use your ODBC control Panel to creat the base DSN to your file as the info changes with each version of Excel.

I've never re-linked like this, but theoretically, you should be able to write a VB Module within Access to open the link.

[jireh]

I agree with techtix. I think you should create two connections, one for excel and one for ms access so that you don't have to change every time you open the other application.