Append many table from ACCESS to SQL server Table

[antonyel]

HI Friends,

could you help me some advice how to simple way to append multiple access table (20 table simultanouly) to SQL Server Table
sample on attachment

everything is the same
format header and name in access and sql server are the same.

my idea is i have around 20 table from access need to import to SQL Server everyday

previously i did with import task from sql server but is not efficient because i have to select many table and the correct table on access and take some time and the probability to doing sometime wrong is big :banghead:

many thanks

 

[boerbende]

Use database tools, export to sql server in Access self
Works like a charm for me

 

[boerbende]

By the way, I read too late you want to do this this every day. I thought it was a one timer.

In that case you could
1) link the SQL server tables as linked table to your access database (via External data / ODBC database) and
2) create append queries in access from your local tables to the linked tables

 

[isladogs]

I meant to reply to this hours ago and was going to say exactly what boerbende wrote in the previous reply.

However before doing so, rename each local table e.g. With suffix LOCAL.
Then run the append queries.
Finally delete the LOCAL tables as they are no longer needed.
The linked SQL tables are now live so you never need to do this process again.

NOTE if you have multiple users of this database, lock it before you start so it's not being updated by other users

Hope that helps

EDIT for info the export to SQL Server feature was deprecated in Access 2013. However a much more powerful import feature is still available within SQL Server

 

[antonyel]

By the way, I read too late you want to do this this every day. I thought it was a one timer.

In that case you could
1) link the SQL server tables as linked table to your access database (via External data / ODBC database) and
2) create append queries in access from your local tables to the linked tables

Hi Sir,

from your explaination i got your idea and i have tried before, the steps that i did is
1. import by wizard all the table that i need from access
2. i create the ODBC Connection to sql server
3. from access db i did connection from external db to all my table in sql server.

the problem is I can not modify all the data I have linked from sql before.
i show some explaination on youtube. this gay if he modified the access db its also change in sql server. i don know way.. this is the result
thanks

 

[isladogs]

Sorry but I'm not sure what your reply means.

You cannot change the DESIGN of SQL LINKED tables from Access but you can edit the data & add new records

If you want to change the design you do that in SQL server manually or using a script.

For linked Access tables, it is possible to change the DESIGN of linked tables using code though in many cases its easier to just open the linked database & do it there.

You said the names & structure are the same in local Access tables & in SQL Server so no need to change the design anyway!

So for editing the data in SQL server,
a) rename your local Access tables e.g. A_RNAC_LOCAL
b) link your SQL server tables & use the original names e.g. A_RNAC (dbo part isn't transferred)
c) run your append queries (or update / delete queries if appropriate)
d) delete your local tables

You now have a FE with a linked SQL BE so you won't ever need to do this again as the links are obviously LIVE

As the linked SQL tables have the same names as your local tables originally had, all your queries & functions will still work exactly as before

HTH

 

[antonyel]

I meant to reply to this hours ago and was going to say exactly what boerbende wrote in the previous reply.

However before doing so, rename each local table e.g. With suffix LOCAL.
Then run the append queries.
Finally delete the LOCAL tables as they are no longer needed.
The linked SQL tables are now live so you never need to do this process again.

NOTE if you have multiple users of this database, lock it before you start so it's not being updated by other users

Hope that helps

EDIT for info the export to SQL Server feature was deprecated in Access 2013. However a much more powerful import feature is still available within SQL Server

Hi Sir,

I do not understand the meaning of your explanation or maybe I'm still too new in the database

thanks

 

[isladogs]

Posts crossed - does my second reply make better sense?

 

[antonyel]

Sorry but I'm not sure what your reply means.

You cannot change the DESIGN of SQL LINKED tables from Access but you can edit the data & add new records

If you want to change the design you do that in SQL server manually or using a script.

For linked Access tables, it is possible to change the DESIGN of linked tables using code though in many cases its easier to just open the linked database & do it there.

You said the names & structure are the saqme in local Access tables & in SQL Server so no need to change the design anyway!

So for editing the data in SQL server,
a) rename your local Access tables e.g. A_RNAC_LOCAL
b) link your SQL server tables & use the original names e.g. A_RNAC (dbo part isn't transferred)
c) run your append queries (or update / delete queries if appropriate)
d) delete your local tables

You now have a FE with a linked SQL BE so you won't ever need to do this again as the links are obviously LIVE

As the linked SQL tables have the same names as your local tables originally had, all your queries & functions will still work exactly as before

HTH

Hi Sir,

now i understand with your idea, lets me follow the steps and try i will update later on

many thanks

 

[boerbende]

About your non updatable linked tables:
What I think when I see the table is that you either don't have an index or you have specified a (wrong) index when linking.
SQL server only allows an update via a linked table when you have an unique index in the table on the server. You also need to have the index in your update query in Access, but you don't need to enter data for the index. Only for the other fields

I have also experienced that the ODBC link was asking to give a unique field. It can happen that it only works when you ignore this question (select nothing and click on OK)

Ben