Insert Data from an Access table to a similar table in MS SQL Server

[cheekybuddha]

but it takes a LOOP to collect the thousand records.

No it doesn't - it's one SELECT call for every thousand records.

If there are fewer than a thousand records, then it's just one query.

The .GetString method builds the concatenation in a single call (very fast!) - but it will be a single query to run on the server.

As I may have mentioned, I haven't tested at all! So it may have no benefit - I'll try and do some tests this week if I get time.

 

[Pat Hartman]

And how are you assembling the string? You are using a loop to collect the data to build the string. You are reading 1000 records to build the string. That takes a loop.

 

[cheekybuddha]

ADODB.Recordset.GetString method

 

[ebs17]

Detached from previous suggestions: A normal query via Jet could also be used.

MS-Tabelle in MS-SQL Express importieren - MS-Office-Forum

Hallo zusammen, Access-Tabelle MDB soll in MS-SQL 2017 64 Bit importiert werden Beim SQL-Impo0rt ist nichts für ACCESS als Importdatei vorgesehen. Einer me Idee mfg Thomas

www.ms-office-forum.net

markusxy says: An append query takes 15 minutes, a comparable make table query only takes 18 seconds, he hadn't figured out the reason for this yet.
With this time difference, you can easily execute an additional append query from the new table to the destination table, which can then be used as a PT.

SELECT *
INTO [ODBC;Driver={ODBC Driver 17 for SQL Server};Server=xxx;Database=xxx;UID=xxx;PWD=xxxxxxxxxxxxx;].newtable
FROM AccessTable

 

[Pat Hartman]

I'm sure that there's at least 5 other ways to avoid the simple solution of an Access append query.

 

[June7]

I suppose GetString() function executes a loop behind the scenes.

Another discussion https://stackoverflow.com/questions/24890695/how-to-use-local-db-table-in-a-pass-through-query

If you can figure out the connection string for Access db, try OpenDataSource or OpenRowSet in pass-through for INSERT INTO SELECT.
https://stackoverflow.com/questions/25627848/opendatasource-and-ms-access-syntax

 

[Josef P.]

Note:
In the example in #24, you can also use (global) temporary tables (e. g. ##GUID-string) as the target and then use a direct sql statement (or call a sql procedure) via pass-though query or ADODB/OLEDB connection to insert the data into the original table.

The problem is that each data record is transferred individually via ODBC. If a trigger then runs in the target table, for example, it runs for each individual data record.

With Insert into Tab (...) select ... an Insert SQL statement is executed for each data record.
Profiler log:

exec sp_executesql N'INSERT INTO  "dbo"."Tab123"  ("ID","P","Z") VALUES (@P1,@P2,@P3)',N'@P1 int,@P2 nvarchar(255),@P3 int',1,NULL,NULL
exec sp_executesql N'INSERT INTO  "dbo"."Tab123"  ("ID","P","Z") VALUES (@P1,@P2,@P3)',N'@P1 int,@P2 nvarchar(255),@P3 int',2,N'JP16',NULL
...

With select ... into ... the insert statement is converted into a temp procedure and this procedure is called for each data record.

declare @p1 int  set @p1=1  exec sp_prepexec @p1 output,N'@P1 int,@P2 nvarchar(4000),@P3 int',N'INSERT INTO "Tab123" VALUES (@P1,@P2,@P3)',1,NULL,NULL  select @p1
exec sp_execute 1,2,N'JP16',NULL
...

 

[CJ_London]

An append query takes 15 minutes, a comparable make table query only takes 18 seconds

Not sure about 15 minutes v 18 seconds but a ‘performance enhancer’ for appends is to remove all indexes from the destination table, append the new data then recreate the indexes.

care required re potential duplicates and other constraints of course

Can’t confirm it as not at my computer but pretty sure a make table does not create indexes but if it does it adds the data then creates the indexes

 

[Josef P.]

Note on David's idea:

You are using a loop to collect the data to build the string. You are reading 1000 records to build the string. That takes a loop.

It then makes a bulk insert on the SQL-Server side.
And since reading takes much less time than writing, this method can certainly be an advantage when transferring.
(Instead of a PT query, you could also implement the insert via ADODB. However, this should be similarly fast.)

In .net you could pass a DataTable to a procedure. Unfortunately, this is not so easily available for VBA.
Alternatively, you could consider transferring the data as JSON or XML to an SQL procedure.
I would also test whether an ADODB/OLEDB recordset with UpdateBatch is already running fast enough. (But this is also not a bulk insert on SQL-Server side).

However, I would only use these variants if the connection to the SQL-Server is so bad that the simple insert statement via the linked table cannot be implemented.
If it is my SQL-Server, I would try to find out where the bottleneck is. Is it the transfer of the data, the processing of the individual data records in the table, etc.

 

[nector]

Many thanks

cheekybuddha​

I will surely settle on your solution , also warm regards to all the contributors all your answers are valuable to me.

 

[Pat Hartman]

I will surely settle on your solution

Did you at least try the simple solution first?

 

[nector]

Yes sir, from my own experience, using the SQL server can only be good if you are running Views for reports. But using bound forms to the table with the form's property turned to data entry still in terms of speed it is slow to about 25 seconds compared to a form bound to an MS Access table. Worse off if there are a couple of combo boxes. Even if people claim that you need to filter the record set attached to the form , but a form which has the property on with data entry and all combo boxes filled with lazy combo sand box which loads with absolutely no data at all , it does not open or save data instantly it takes about 26 seconds or after some the so called catching to somewhere around 10 seconds.

True reports bound to a view or pass through query in terms of speed it can match Ms Access. If Microsoft can increase the storage capacity for Ms Access to at least 200 GB then this server thing will have no market at all and I think that is the reason they cannot do that to ensure that the server does not die.

 

[Pat Hartman]

Yes sir, from my own experience, using the SQL server can only be good if you are running Views for reports. But using bound forms to the table with the form's property turned to data entry still in terms of speed it is slow to about 25 seconds compared to a form bound to an MS Access table. Worse off if there are a couple of combo boxes. Even if people claim that you need to filter the record set attached to the form , but a form which has the property on with data entry and all combo boxes filled with lazy combo sand box which loads with absolutely no data at all , it does not open or save data instantly it takes about 26 seconds or after some the so called catching to somewhere around 10 seconds.

True reports bound to a view or pass through query in terms of speed it can match Ms Access. If Microsoft can increase the storage capacity for Ms Access to at least 200 GB then this server thing will have no market at all and I think that is the reason they cannot do that to ensure that the server does not die.

Either you are doing something wrong like binding your forms to tables or queries with no criteria and using Access to filter locally or you are on the slowest LAN known to mankind. You shouldn't be having any trouble using bound forms and Access queries for the bulk of your work. Sometimes you will need Views to speed up certain joins and occasionally you will need pass through queries for some bulk updates or complex reports.

 

[nector]

Either you are doing something wrong like binding your forms to tables or queries with no criteria and using Access to filter locally.

With total respect to what you are saying all my data entry form have their data entry properties set to "YES", and so they open without a single record at all, all my combo boxes open empty until the users type in the Combo box the first 3 characters only then can a small or short list pop up for the matching words and then users select from there.

Example below is a code without a sand box to initialize the combo box

Option Compare Database
Option Explicit
Private INVECCustomerIDLookup As New weComboLookup

Private Sub Form_Load()
    INVECCustomerIDLookup.Initialize Me.CustomerID, 3
    INVECCustomerIDLookup.UnfilteredRowSource = "SELECT CustomerID,Company FROM tblCustomers WHERE Company Like '**'ORDER BY Company;"
    INVECCustomerIDLookup.IsLazyLoading = True
    Me.CustomerID.Requery
End Sub

Example of a binded form to my table, see photo, as you can see we do not edit any data just direct data entry not editing at all. These forms open empty no data at all.

 

[Minty]

I'm not sure if setting a form to data entry stops it loading the entire table in the background?
What if you set the recordsource to tblCustomerInvoices where 1 = 0 ?

 

[nector]

If you are suggesting some like below:

DoCmd.OpenForm "frmCustomerInvoice", Where [InvoiceID] = 0"

 

[cheekybuddha]

If you are suggesting some like below:

DoCmd.OpenForm "frmCustomerInvoice", Where [InvoiceID] = 0"

Probably more like:

DoCmd.OpenForm "frmCustomerInvoice", Where:="1 = 0"