473,320 Members | 1,953 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Linking Access tables with SQL tables using File DSN through VBA

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:
Expand|Select|Wrap|Line Numbers
  1. [ODBC]
  2. DRIVER=SQL Server
  3. UID=user1
  4. PWD=password1
  5. DATABASE=MyDB
  6. APP=Microsoft Office 2005
  7. 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.
Jan 8 '13 #1
3 2453
NeoPa
32,556 Expert Mod 16PB
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.
Jan 9 '13 #2
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
Jan 9 '13 #3
NeoPa
32,556 Expert Mod 16PB
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.
Jan 9 '13 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Matthew Wells | last post by:
Good morning... I have an Access front end that uses SQL Server linked tables. SQL Server uses Windows authentication. I have one Windows group that all Access users are a member of. I added...
0
by: ChanzReed | last post by:
We are in the process of converting Access 2002 tables into MS SQL. I used a file DSN located on a spot on the network I KNOW everyone has access to and it works for the lion's share of users....
2
by: Jeff Pritchard | last post by:
Some time ago I am sure I came across something that said this was possible, though it doesn't seem to work. A client wants to replace an Access back-end with SQL Server tables. We have tried...
1
by: anthony.k.farrant | last post by:
Hello everybody, I am new to Access. I have to rewrite an app built with Access upon SQL Server. Access links to SQL Server with linked tables. Now i'd like to change the connection properties...
1
by: lochmant | last post by:
I have a series of about 10 test databases with about 34 linked table each. I need to create an Access 2003 database front end for each of these. I am currently trying to write a script that will...
17
by: aquablade | last post by:
I have an Oracle 10g R2 database where my ERP transactions reside. I'm using MS Access to grant access to my power users where I use MS Access' Link Tables to provide direct access. I noticed that...
1
by: Lavern | last post by:
Hello, I have set up a database in MS SQL Studio Express and created an ODBC (System Data Source) so I could open my database in Access. I created a file in Access and choose my ODBC for my...
0
by: asorkoram | last post by:
Hello, I have a 3rd party app that uses SQL server to store its data. I am also creating a MS Access 2003 app to access the data. When I import the tables a few of the tables have over the 32 index...
0
by: asorkoram | last post by:
Hello, I have a 3rd party app that uses SQL server to store its data. I am also creating a MS Access 2003 app to access the data. When I import the tables a few of the tables have over the 32 index...
3
by: Bret Kuhns | last post by:
I recently started a co-op/internship at a company and they are looking to migrate a large legacy supported application from OLEDB to SQL Server. I'm doing prelim work in experimenting with the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.