Linked Tables

[Guest]

I want to to change all the links in the linked tables in an mdb file to
point to sql server database.

If there's a way to do this by writing some kind of script (vba or even
..net) or any other way (using the Linked Table manager I can only change the
links to point to another file) I'll be happy to know.

If you know how to do this using the access upsize wizard (which mean using
the same data that in the mdb files) , I will be interested to hear too. I
think this is similar to the question here

http://www-new.experts-exchange.com...ess/Q_21916141.html?qid=21916141&qid=21916141

(The problem is that the upsizing wizard skips linked tables by default)

 

[punjab_tom]

uh right click link, files of type = ODBC

from there you can do it I think without a problem
if not; you can write a little script to do it

but the bottom line is that linked tables against SQL Server is kinda
pointless; you really should be using ADP
it is much better, faster, more stable

and it will leave you with a scalable SQL Server infrastructure
instead of some laughable piece of crap MDB junk

MDB has been obsolete for 10 years; get with the program, kid

-Tom

 

[punjab_tom]

oh sorry now I think that I understand your question

0) take a backup of everything
a) delete all the linked tables from your MDB
b) IMPORT all the linked tables from the MDB backend
c) run upsizing and choose project

hope that helps

 

[Guest]

In case you got here snd wonder how to do this by code:
Sub execute()


Dim tdf As TableDef, db As Database
Set db = CurrentDb
db.TableDefs.Refresh

For Each tdf In db.TableDefs
With tdf

If Left$(.Connect, Len(";DATABASE=")) = ";DATABASE=" Then
'checks if this table is linked to a file

.Connect = "ODBC;DRIVER=SQL
Server;SERVER=MYCOMPUTER\SQLEXPRESS;APP=Microsoft Office
XP;WSID=MYCOMPUTER;DATABASE=gendbSQL;Trusted_Connection=Yes"

tdf.RefreshLink

End If
End With
Next

Set tdf = Nothing
Set db = Nothing
End Sub

 

[another_username_2]

Hi Tom

I have originally posted this question on the Microsoft original
newsgroup so I haven't seen your posts until yesterday, when I looked
for my display name at google...

Thank for the advices, using import before the upsizing is a good Idea
and will reduce the number of upsizing I need to do (I have 3 external
mdb's). Do you know if upsizing can be done by VBA code too?
I have transferred my application to adp, but since I am using DAO's
CurrentDB in a lot of places (We have about 8 different applications,
each one has about 82 forms and 15 modules, which also means it took
me 3 hours to generate the ADP file for one of them) I will have to
change a lot of code, so I decided to skip that for now. If you know
how to solve this problem without changing my code manually, I'll be
happy to hear about it.
If we intend to spend time on converting from DAO to ADO then maybe
we should go all the way and migrate to .NET ("get with the program
kid" - I think Access is the real obsolete and not just mdb - look how
much trouble I have because of that upsizing, and it's not all, look
at my thread at
http://groups.google.com/group/micr...nk=gst&q=entrylevel26&rnum=1#1cf4c251802e1ede
.... This stuff would never happen in Java, where the number of
parameters is constant)
Plus, ADP file has other disadvantages, for instance I can't maintain
local tables (for each user preferences - I can workaround it by
saving them in one table but still..) and I need to take care that all
my queries will have a sql-procedure syntax. Are these two problems
solvable? Can I mix my ADP with local tables/queries?
Is the performance really that better in an ADP file? Making the
queries SQL procedures is definitely an improvement, but does it take
off all JET processing that should remain after transferring to SQL in
an mdb file? Do you have some documentation that says that explicitly?

Thanks, Ethan

 

[another_username_2]

oh sorry now I think that I understand your question

0) take a backup of everything
a) delete all the linked tables from your MDB
b) IMPORT all the linked tables from the MDB backend
c) run upsizing and choose project

hope that helps

I think that another benefit from this strategy (rather than upsizing
the backend and relinking to it) is that we make sure relationship
constraints will be manged by sql only and not by sql and a anothe
layer (which could be redundant in the case of inner MDB relationship
or not redundant in the case of relationship between two tables in
different mdb's) by access, what do do you think?