Copy Access tables to Access Project with SQL Server tables

[Kaykayme]

I have an Access project database that has linked tables to SQL Server. I
want to copy current data from another Access database to the Access project
with the linked tables. A previous developer is using the
DoCmd.TransferDatabase command to copy these tables but I don't think it is
working since I opened the tables in the Access project and found that the
last records were added in 2007. Any suggestions?

 

[Sylvain Lafontaine]

Well, as such, an Access Database Project (ADP) doesn't have ODBC Linked
table but the Server to which it is linked can have.; so I'm not sure what
you mean exactly with "Access project database that has linked tables to
SQL-Server".

If you are using a MDB or ACCDB database file with ODBC linked tables, you
can make an insert query on these tables in exactly the same way as if they
were ordinary tables; so you don't have to use the DoCmd.TransferDatabase at
all.

Are you sure that you are looking at the right place? If the last entries
are really from 2007, I suspect that you should have had some other problems
since then.

 

[Kaykayme]

Please let me explain. There are TWO Access databases, one is a mdb and one
is a adp. July 2007 is when the creator of these database ended employment
here and since then other users have been trying to unravel these databases
to produce updated reports exported to a Sharepoint server. What I see that
the last user did is upload data from spreadsheets to the mdb database, copy
those tables using the TransferDatabase method to the adp database and then
using stored procedures on SQLServer (where all of the adp tables and queries
reside) to update the tables in the adp database. However, because of some
errors in the import/export procedures and because not all of the updated
data is imported into adp we are not able to produce the reports with all of
the updated data.

This seems to be just a matter of stepping through the procedures to find
errors and adding procedures to import all necessary data.

My goal since we are using Access 2007 is to convert this whole process to
an Access 2007 accdb then upload the database to Sharepoint. Any suggestions?

 

[Sylvain Lafontaine]

An ADP project is not a database: there is no data, table or view in a ADP
project. All it has are forms and reports and a connection string to a
SQL-Server where the tables, datas, views, UDF, SP, etc. reside; so saying
that data have been transferred to an ADP project using the TransferDatabase
make no sense to me. However, what the TransferDatabase method might have
done is to retrieve the connection string from the ADP project and use it to
transfert the data directly to the SQL-Server. In your case, stop looking
at the ADP project and take a look only at the SQL-Server. This is were
your data are.

You're also correct in saying that often the TransferDatabase will not be
able to correctly transfert all the data. Personally, I don't remember the
last time that I tried to use this buggy method.

For the rest, as you don't have the intention of keeping SQL-Server anymore,
I would suggest that you modify your procedures so that after the data have
been imported from the Excel spreadsheets to the Access database, that they
are to be transferred immediately to the Sharepoint server instead of trying
to transfert them to the SQL-Server before re-migrating them to the
Sharepoint server.

 

[Kaykayme]

Thanks so much for your help. If I use SharePoint lists instead of SQL
Server are there any limitations in SharePoint that I should know about?

 

[Sylvain Lafontaine]

No idea, I don't use SharePoint myself. I would suggest that you post these
kinds of questions in a newsgroup dedicated to SharePoint.