DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

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

•

Join Date: Sep 2008

Posts: 2

Reputation: sai.baranidhara is an unknown quantity at this point

Solved Threads: 0

sai.baranidhara sai.baranidhara is offline

Offline

Newbie Poster

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

Sep 22nd, 2008

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

•

Join Date: Apr 2008

Posts: 34

Reputation: techtix is an unknown quantity at this point

Solved Threads: 4

techtix

Offline

Light Poster

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

Sep 23rd, 2008

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

[Example DSN]

 Help with Code Tags 
 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.

•

Join Date: Jul 2007

Posts: 312

Reputation: jireh is an unknown quantity at this point

Solved Threads: 47

jireh

Offline

Posting Whiz

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

Sep 23rd, 2008

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.

A conclusion is the place where you got tired of thinking.
http://www.martin2k.co.uk/forums/index.php?showforum=4
http://www.a1vbcode.com/a1vbcode/vbforums/Forum3-1.aspx
http://www.developerfusion.co.uk/for...orum&ForumID=4