Access using SQL Server only DB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have converted a project from Access DB to use SQL Sever DB. But I still
have the .mdb tables left as TAB1_local. How can I convert from Access to an
..adp project using ONLY SQL server tables. I tried but then the forms do not
work! Just saying "An Error Occurred" when I activate the form(s). Maybe it
is ok to have the local tables left but they disturb me - I hate them when I
see them!
 
I recommend you do NOT use an .adp.

Use the same .mdb you started with.
Make a copy of it.

Now check that all the data is in SQL Server.
Then delete all local Access tables (that are now in SQL Server.)

Now link to SQL Server and link to the tables you just moved there.
If you do the link manually you have to remove the dbo_ prefix from each
table name so that they are named the exact same way as when they were local
Access tables.

Now your app should work.
All queries and forms and reports and code will use the linked tables the
same way they used the original ones.

Then you need to optimize your app so it works better with SQL Server.
(You can do this 1 step at a time.)
But at least now you should have a functioning app.

BTW - even MS recommends linked tables over .adp projects.
 
Thanks Joe,
that was very helpful. It all works except for one report. Unfortunately the
only thing I see when I run it is "An error Occured". Is there a log
somewhere? I'm a bit suspicious because the report is getting info from two
tables that have a relation where the parent and child table are related via
a column that is NOT Primary Key in the parent. As you know there is no way
of creating such a relationship i SQL Server!
 
I recommend you re-write that report to use a SQL Pass Through Query so you
can do the join on the server and then get back a read only result set. This
is one of the optimizations I was referring to.

Try not to join linked tables in your local queries. Always try to send them
to SQL Server as SPT queries instead.
This way Access does not try to download huge amounts of data to resolve the
join.
 
Back
Top