VBA to Append Access data to SQL Server Table (1 Viewer)

I am working with MS Access 2010 and SQL Server 2008 R2.
Currently, I use .ADPs and VBA to append data into the SQL Database table(s). I understand that in Office 2013, "ADPs are Dead!". I am looking for best practice Access VBA examples that append data from an Access table into a SQL Table. Currently, with VBA, I can link the SQL table and use vba to append the data, but that method runs very slow; I'm looking for a faster programmatic process, perhaps using a Pass-Through query.

Any help will be appreciated. Thanks in Advance

Some of us would argue ADP's were never alive to begin with.

You can certainly build the SQL of a pass-through query and run that. It could be the actual insert SQL or a stored procedure call with the appropriate parameters. You can also execute a stored procedure using an ADO command object.

FYI I moved your thread from the introductions forum.

The SQL Linked tables will work just like the local Acceess tables.
Are you looking to append a single record at a time, or DB.Execute an Append SQL statement?
e.g. All records in the local DB that have a specific criteria appended to the a SQL Server table
As Paul mentioned, there are several other methods too. Let us know how many records or what method drives the Append.

This is my solution for "sending" data through a passthrough qry to a SQL-Server table. It would not be neccessy to link the tables in your access project.

1) Build a function with the connection string:
'If QueryName is not provided or is an empty string ( = "" ) no query object will be created but the SQL statement or stored procedure will be executed (useful for DELETE, INSERT, UPDATE statements).
'If you provide the parameter QueryName a query will be created with the provided name (this is useful for a SELECT statement as you expect to retrieve the resulting data set).

Function SQL_PassThrough(ByVal SQL As String, Optional QueryName As String)
Dim qdf As querydef
'On Error Resume Next
Set qdf = CurrentDb.CreateQueryDef
With qdf
.Name = QueryName
.Connect = "ODBC;DSN=myDSN,UID=myUID, PWD=myPW;APP=Microsoft Office 2010;DATABASE=MyDatabase;"
.SQL = SQL
.ReturnsRecords = (Len(QueryName) > 0)
If .ReturnsRecords = False Then
.Execute
Else
If Not IsNull(CurrentDb.QueryDefs(QueryName).Name) Then
CurrentDb.QueryDefs.Delete QueryName
End If
CurrentDb.QueryDefs.Append qdf
End If
.Close
End With
Set qdf = Nothing
End Function

2) Build your stored procedure on the SQL-database

3) execute the stored procedure in Access

....
Dim strSQL as string

strSQL = "EXEC dbo.spMyStoredProcedure " & AnyParameter

Call SQL_PassThrough(strSQL, "PT_LookUpIrgendwas")

If you want to retrieve data back from the SP, you have to name the retrieving query

Call SQL_PassThrough(strSQL, "PT_LookUpSomething")


HTH
Michael

Rx,
I am looking for a faster way to transfer data from a Access 2010 database to a SQL Server 2008 R2.
The number of records varies; this is a daily task and has had upto 60,000 records and as little as 300 records. These records are our daily transactions; all of the daily transactions, whatever the number, are transferred from Access to the SQL Server.
I am converting from an .ADP to a .ACCDB and use VBA to handle most of the work.
I have found that any of our accountants can extract data by the tens of thousands from the SQL Server table to Excel of Acces in mere seconds based on the criteria they provide; unfortunately, it takes considerably longer time to perform the once daily task of transferring these records into the SQL database.

Currently I have VBA to link, via a parameter of a called subprocedure, to the SQL table (in fact, it will link to any table name passed to the subprocedure).

Then I use the following to append the daily data to the SQL table:
DoCmd.RunSQL "Insert into tbl_Imported_Projects (Bus_Unit, Oper_Unit, FY, Period, Dept_ID, Class, Acct, Proj, Act_ID, Fund, Res_Type, Res_Cat, Amount, ID, Trans_Date, Actg_Date, Journal_ID, Vendor_ID, Voucher_ID, User_ID, Bus_Unit_AP, Anal_Type, Jrnl_Date, Date_Time_Stamp, Downloaded) " & _
"Select Bus_Unit, Oper_Unit, FY, Period, Dept_ID, Class, Acct, Proj, Act_ID, Fund, Res_Type, Res_Cat, Amount, ID, Trans_Date, Actg_Date, Journal_ID, Vendor_ID, Voucher_ID, User_ID, Bus_Unit_AP, Anal_Type, Jrnl_Date, Date_Time_Stamp, Downloaded " & _
"From NDS325_RT_PROJ_3A"

This process takes minutes, not seconds, to run. I am hoping to find a faster method that is not overly complex; ergo, my request for sample code.

I am familiar with using command, command type, execute, etc. and use that often to run the SQL Stored Procedures from Access and/or to extract data from the SQL table; but am not using it to transfer data into the SQL table ... maybe I should be???

Hope that helps. Thanks again.

Tiger955,
I appreciate your response. It appears you are using DAO instead of ADO. From what I have read, it said if you are going to use DAO to transfer any significant amount of records into the SQL Server table, plan in taking a coffee break (or two) while you wait. They strongly recommend you use ADO.

This is a key question. Is the data being inserted coming from a Local Acces Table or a SQL Linked Table?
If the data being inserted to SQL is a local table:
- Passthrugh insert queries won't work - unless run entirely on the server.
- Trying to set implicit_transactions ON in the MS-Access connection session for instance, to force MS-Access not to commit after each and every insert will have no effect.
then DAO has little (or no control) over the Insert and typically will conduct it row by row.

Re ADO: It might be possible to create a stored procedure on the SQL Server side and then call the ADO using an ADO command.

Option: Using SSIS - create an import package to pull the data into your SQL Server database rather than push data from Access. The process is much faster.

As a rule, for large datesets with local / Linked SQL Tables, follow this:
Don't use query constructs that cause processing to be done by Access on the local computer.


The following query operations force the Jet database engine to perform local data processing:

• Join operations between table that are linked to different data source (I.e. a join between a SQL table and a linked Access table)
• Join operations based on query that uses the DISTINCT keyword, or a query that contains a GROUP BY clause.
• Outer joins that contain syntax that is not directly supported by the remote database server.
• The LIKE operator used with Text or Memo fields
• Never use VBA formulas in the query

Just wondering, if there was a single SQL linked table that perfectlly matched the Local Table's data structure (i.e. clone).
- Delete all data in the SQL Linked clone. Append the data to be updated from the local table only using a filter, no joining criteria.
Then call on the two SQL Linked tables for the update. (SQL Linked to SQL Linked Clone).

Will let you digest all of this and provide more valuable feedback. You probably already know much of this, but it might be helpful to get the options focused for a solution.

Later in the day, I was looking at Json for SQL Server.
Came across this article. It might be useful to your situation.
http://www.postgresonline.com/journal/archives/288-How-to-bulk-export-tables-from-MS-Access.html

If your bulk updates grow by 10 times it might be worth looking at a SOAP importer.
It looks like a big project to me.
http://www.codeproject.com/Articles/538442/Generating-JSON-Web-Services-from-an-Existing-Data