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

Expand Post »

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

Similar Threads

memory management in wndows 2000 in Windows NT / 2000 / XP

sai.baranidhara

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Sep 2008

Permalink

Sep 23rd, 2008

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

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

[Example DSN]

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
[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
[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.

Last edited by techtix; Sep 23rd, 2008 at 6:53 pm.

techtix

Reputation Points: 12

Solved Threads: 4

Light Poster

Offline

34 posts
since Apr 2008

Permalink

Sep 23rd, 2008

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

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.

jireh

Reputation Points: 11

Solved Threads: 49

Posting Whiz

Offline

316 posts
since Jul 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Encoding in Vb 6.0

Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: filter on dropdown combobox.

DaniWeb Message

Cancel Changes

User Name
Password