changing source of linked tables.

Question

changing source of linked tables.

jack-64275

SSCrazy

Points: 2017
More actions
September 11, 2003 at 8:16 am

#59483

We have just moved a database from Access to SQL 2000. An 'application database', which contains all the queries reports and forms, was linked to this Access database. We now want to change the link to point to the tables in SQL so that the reports, forms, and queries can still be used.
Any ideas?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

quote:
We have just moved a database from Access to SQL 2000. An 'application database', which contains all the queries reports and forms, was linked to this Access database. We now want to change the link to point to the tables in SQL so that the reports, forms, and queries can still be used.

can't you use the Link table Wizard ?!?

I mean the wizard under Extras->7th entry from above 3rd entry from above

Sorry, for this translation. I don't know the english words!

If you want to do this programmatically, I think womalley has posted an example to this forum yesterday.

Frank

Wenn Englisch zu schwierig ist?

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 2

Fisrt create DSN to your SQL Server, and create linked tables in Access database via the DSN to SQL Serevr. The linked table name should be same as your original Access table name.

All tables in SQL Server should have primary index.

jack-64275 SSCrazy Points: 2017 More actions · Answer 3

quote:
Fisrt create DSN to your SQL Server, and create linked tables in Access database via the DSN to SQL Serevr. The linked table name should be same as your original Access table name.
All tables in SQL Server should have primary index.

Do I need to remove the existing linked tables first?

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 4

No, you don't but you need rename them and give the new linked tables with the old name.

Edited by - allen_cui on 09/11/2003 09:32:10 AM

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

quote:
Fisrt create DSN to your SQL Server, and create linked tables in Access database via the DSN to SQL Serevr. The linked table name should be same as your original Access table name.

oh my , that's what I long winded wanted to say

quote:
All tables in SQL Server should have primary index.

in fact, if they don't have one, Access pops up a screen where you can choose a field from that table. But it is really better to have one.

Btw, Allen, do you know what to do when the Primary index consists of more than one column?

Frank

Wenn Englisch zu schwierig ist?

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

jack-64275 SSCrazy Points: 2017 More actions · Answer 6

Thank you! We're in business again with the Access front end and SQL back end.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 7

If table already has the composite primary key, it will be reconagized when you create the linked table from Access. IF the table doesn't have primary key, during the creation of linked table, you can choose multiple columns from window 'select unique record identifier'.

jack-64275 SSCrazy Points: 2017 More actions · Answer 8

We created all the PKs in SQL then linked the tables.