linking access and sql tables

[hamking01]

I'm working on an Access database that people have developed over the years. Relationships and data integrity haven't been set up correctly. The mdb's are getting corrupted everyday in addition to many other problems. Hesitant of moving it to SQL Server as no one has clear as to how it will affect the entire application (numerous people have created their own Access front-end's that link to the data.

I'm trying to link the SQL Server tables to the Access tables and want to slowly convert everything over to ASP.net, part by part. But I want to ensure everything continues to work in Access, while I work on the ASP.net application with the SQL Server data in the back-end.

Basically, users continue to use their Access mdb's, while all the data that is inputted into the Access db will update the SQL Server table. All articles seem to link table to dump data onto SQL Server.

I've also posted this in SQL Server: Programming and ASP.net forums

 

[lupins46]

'I'm trying to link the SQL Server tables to the Access tables '

There is no such concept in Access.
The tables can only be in one place. In your case the tables would have to be in SQL Server and the Access front ends would have to link to the SQL Server tables.

 

[scroce]

my 2 cents -

i think you or someone needs to spend the time and effort to normalize the database. It's a dirty job, but it'll never work right if you don't do it, and the monster will keep getting bigger and bigger.

I think I know what you mean about "linking" the tables - I think you can do this by setting up a System DSN link to the SQL Server, and then choosing File>Get External Data to point the the DSN - what you get is a dbo_tableName link with a globe icon next to the link, indicating the files reside on the SQL server and not in a Jet DB

but the problem I have run into with this is network overhead. It slows the whole contraption down and seems to eat up resources on my network.


I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[hamking01]

I don't actually want the data to reside on the SQL server. I'm trying to have Access and Sql Server update each other anytime the front-end inputs/modifies data to a specific data. The ASP.net application would easily do this as I can add code to the to update the Access tables. But I also need to have the Sql server updated if a user updates info into Access.

Access application is currently already in production, though it keeps breaking down, it does it's job. I'm trying to convert parts of it to ASP.net part by part. Company is unwilling to invest time to change app at once, even unsure if it'll succeed. More accepting if it's converted over time with progess.

 

[danvlas]

You could try to upsize only the tables to SQL Server, then link the tables to the front-ends through ODBC and see if all functionality remains in place (especially inserts/edits/updates/deletes).

You will definitely need a primary key set up on all tables.

This kind of linking uses the Jet engine to process the data, so the queries/forms/reports should work exactly as in Access.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[scroce]

i've tried what danvlas was saying before, and I ran into performance issues, so you may want to beware of that. but on another network, i tried the same thing and it worked fine, i guess it will depend.

did some reading and some posting to tek-tips on this topic, if you'd care to glance at it. check out thread962-805951

I am a nobody, and nobody is perfect; therefore, I am perfect.