Linking Access tables with SQL tables using File DSN through VBA

[pradeepatta]

We have an ms access application and tables in that are linked with backend database SQL SERVER 2008

I used to manually link the access tables with SQL tables with the help of 'Table Link Manager' option. Now I want to do this programatically .

File DSN will contain belo information:

[ODBC]
DRIVER=SQL Server
UID=user1
PWD=password1
DATABASE=MyDB
APP=Microsoft Office 2005
SERVER=111.111.111.111,53314

I need some VBA code that will manage reading the data in from the DSN file and then relink the tables using that data. Unfortunatley I don't know where to start. Can anyone help me through this? I'm willing to work at it.

[NeoPa]

Pradeep, I have reworded your question for you. Please read it through and confirm you are happy to approach the problem from that point of view.

If I get a confirmation from you then I will help, but you will still be expected to do much of the work.

When you reply to confirm, you should also provide a list of the table names for those which need to be relinked. This information was missing, but necessary, from your question.

PS. You should understand that normally I would have deleted the question as it requested code be done for you, but I saw that you had gone to some effort to post a question tidily and with most of the required information included. This convinced me you were worth giving a second chance to.

[pradeepatta]

Thanks Neopa for correcting my post, I am new to this forum and that is the reson why I didn't post that as per the forum guidelines.
I am happy with the way you worded my question.

ACCESS tables list:
TWF MERGE 0
TWF MERGE 1
TWF MERGE 2
TWF MERGE 3
TWF MERGE 4

SQL Server list:
TWF_MERGE_0
TWF_MERGE_1
TWF_MERGE_2
TWF_MERGE_3
TWF_MERGE_4

Please help me to re-link the tables. Thanks

[NeoPa]

Pradeep:
Thanks Neopa for correcting my post, I am new to this forum and that is the reson why I didn't post that as per the forum guidelines.

That is a perfectly acceptable situation and a good answer :-) The fact that you were also able to follow instructions well is another good sign that we can work together.

Some questions for you :

1. Do you have any experience with reading data from files in VBA?
   If not, then I suggest an initial task of reading up (using the Help system) on this starting with the Open Statement, and moving on to Line Input # Statement.
2. What is the existing value of one of your linked table's .Connect property?
   Please include this verbatim in your next post.