Need a faster way to insert data from access local table to sql server table on a server

i am not able to find a feasible solution yet looked all over the internet, some say it cant be done others say to export the access data to csv and insert into sql table, some say to use DoCmd.RunSql, some say to use db.execute(sql) most of these methods still take forever, we are talking about 100k records here. please any vba code to make the insert faster will be very very appreciated.  

Dim db As DAO.Database

   Set db = CurrentDb()
   strsql = "INSERT INTO dbo_WorkTable ( ID, [Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], [General Ledger Cost Center], [General Ledger Department], [Work Center], [Pay Period Ending Date], Hours, Amount, Week, [Pay Type Code], [Pay Type Description], [File Number], Name, [HOURLY SALARY], [FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE] )" _
& "SELECT WorkTable.ID, WorkTable.[L Batch ID], WorkTable.[Pay Group], WorkTable.[Pay Group Description], WorkTable.[General Ledger Account], WorkTable.[General Ledger Cost Center], WorkTable.[General Ledger Department], WorkTable.[Work Center], WorkTable.[Pay Period Ending Date], WorkTable.Hours, WorkTable.Amount, WorkTable.Week, WorkTable.[Pay Type Code], WorkTable.[Pay Type Description], WorkTable.[File Number], WorkTable.Name, WorkTable.[HOURLY SALARY], WorkTable.[FULL TIME_PART TIME], WorkTable.ACTIVE_INACTIVE, WorkTable.[HOURLY RATE]" _
& " FROM WorkTable; "

Open in new window

Aravind RanganathanWindows Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
The issue at hand is that you are using the local jet database engine to do the work. I'm not 100% sure, but I believe it is doing it row by row. You could check SQL profiler to make sure what sql statements are actually being executed.

I did something like this where data was stored in a hosted MySQL db on the web. What I did was to create the seperate insert statements as a string, and fire them as a passthrough query. So loop the records locally, creating a big insert statement, then fire it off. In my case, I choose to cut insert in "batches". Basicly when the insert statement reached 50k characters (as I recall) I would fire it off in the passthrough, reset the SQL string, and proceed with my recordset loop.

As I recall it uploaded impressingly fast (5 seconds total as I recall), considering the mySQL backend was in the cloud.
0
 
PatHartmanCommented:
If you asked the question here (and I think I remember it), you should post a link so you don't see the same suggestions again.  In general, a code loop will be the slowest method.  The other methods using append queries or SQL Server methods would always be faster.

Is this a one-time operation or must you do it frequently?  

You didn't show much of the code, I'm assuming that your append query is appending the entire table and not a record at a time.  The problem with running the append query from Access this way is that Access creates a transaction and so it does the transfer in memory and gives you the option to cancel.  This takes a lot of extra resources.  If the table were exported to a .csv or if you imported it by using a stored procedure in SQL Server to "pull" the data from Access, you would get around this transaction issue since in this case Access isn't helping you it is hurting you.

I just saw Anders solution and he has a point.  If you do this via a passthrough query, you get around the problem that Access is causing.  Or, if you can write a sp, you can run the sp from Access as a passthrough query that doesn't return rows and the sp can "pull" the data from Access.
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
Anders Ebro yes it is doing a row by row and my sql server is not on the cloud :(  if you have any sample code of how you created the batches that will be useful for me.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@ PatHartman  this is a frequent operation the user will process records every time she  begins working with a selected batch of data.  the data is inserting one record at a time.  it would be faster if i can append an entire table from access to sql server how could i achieve this?? i went through my question i dont see opening a particular question regarding this however i had asked regarding this on another question. i had to start a new question because i needed more options bud.
0
 
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
Well, you're doing an INSERT for every single row outside a TRANSACTION.

This will take forever, if you have much data.

Start a TRANSACTION, issue all your INSERT statements inside the TRANSACTION, then end the TRANSACTION.

Likely you'll be surprised how fast your loads go, inside transactions.
1
 
Dale FyeCommented:
Another option might be to write the data to a CSV file and use the SQL Server Bulk Insert command to handle the data, but I've found that bulk insert has issues as well.

Another option would be to create a passthrough query, as mentioned by Anders and Pat, but to do it in bulk, writing multiple records at once with a SQL statement that looks like:
INSERT INTO Table (field1, field2) 
VALUES 
('string1','string2'),
('stringA','stringB')
('StringC', 'StringD')

Open in new window

You could probably do this in blocks, with as many as 50 records (or more depending on the number of columns) at a time.

Dale
1
 
Shaun VermaakTechnical Specialist/DeveloperCommented:
TL/DR
Have a look at the Microsoft Sync Framework but as far as I know, it does not support Access. You can use LocalDB or SQL Express though
0
 
aikimarkCommented:
Note: There's a 64K limit on the string length of the SQL you construct to pass to SQL Server
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@David Favor i tried implementing your transaction into my query but its still  slower it does about 533 records per minute and 2834 records for 5 minutes but i have 50 k records this is my revised code:
   DAO.DBEngine.BeginTrans
    
    On Error GoTo tran_Err
    
    CurrentDb.Execute "Delete FROM dbo_WorkTable", dbSeeChanges
    
    CurrentDb.Execute "INSERT INTO dbo_WorkTable ( ID,[L Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], [General Ledger Cost Center], [General Ledger Department], [Work Center], [Pay Period Ending Date], Hours, Amount, Week, [Pay Type Code], [Pay Type Description], [File Number], Name, [HOURLY SALARY], [FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE] ) SELECT WorkTable.ID,WorkTable.[L Batch ID], WorkTable.[Pay Group], WorkTable.[Pay Group Description], WorkTable.[General Ledger Account], WorkTable.[General Ledger Cost Center], WorkTable.[General Ledger Department], WorkTable.[Work Center], WorkTable.[Pay Period Ending Date], WorkTable.Hours, WorkTable.Amount, WorkTable.Week, WorkTable.[Pay Type Code], WorkTable.[Pay Type Description], WorkTable.[File Number], WorkTable.Name, WorkTable.[HOURLY SALARY], WorkTable.[FULL TIME_PART TIME], WorkTable.ACTIVE_INACTIVE, WorkTable.[HOURLY RATE] FROM WorkTable;"
    
    DAO.DBEngine.CommitTrans
    
Exit Sub
    
tran_Err:
    
    DAO.DBEngine.Rollback

    MsgBox "Transaction failed. Error: " & Err.Description
End Sub

Open in new window


please help
0
 
Dale FyeCommented:
Have you tried this as a pass-through query, with SQL string similar to what I posted in this response?
1
 
aikimarkCommented:
If you don't create a pass-thru query (which you should definitely do), you can instantiate an ADODB connection and execute a large SQL string resembling that suggested by Dale Fye
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Dale Fye i cant try pass-through query because one of the table that i am sending the data from is a local access table. from reading online it says that pass through queries work best when you are dealing with two sql server tables.
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@aikimark i cant try pass-through query because one of the table that i am sending the data from is a local access table. i have tried the ADODB connection it is still slow bud.
0
 
aikimarkCommented:
Your pass-thru (or ADODB connection) points to the table you are populating with your data.  You instantiate a recordset object with data from the local MS Access database.  Then you iterate through that recordset, constructing T-SQL in the manner described by Dale Fye.  When you have built up a sufficiently long, but less than 64K, string, you invoke the pass-thru query or the ADODB connection object's EXECUTE method.
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@aikimark based on yours and Dale  Fye 's answer this is my revised code but it is not inserting anything into my sql server table:

Dim db As dao.Database
Dim rs As dao.Recordset
Dim ADOCon As New ADODB.Connection
ADOCon.ConnectionString = "Connection String"
Dim sqlStr As String

sqlStr = "WorkTable"

Set db = CurrentDb
Set rs = db.OpenRecordset(sqlStr)
With rs

ADOCon.Open

rs.MoveFirst
Do While Not rs.EOF

strsql = "INSERT INTO dbo_WorkTable ( ID,[L Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], [General Ledger Cost Center], [General Ledger Department], [Work Center], [Pay Period Ending Date], Hours, Amount, Week, [Pay Type Code], [Pay Type Description], [File Number], Name, [HOURLY SALARY], [FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE],[Load Date],Processed) VALUES" _
& "( rs!ID,rs![L Batch ID], rs![Pay Group], rs![Pay Group Description], rs![General Ledger Account], rs![General Ledger Cost Center], rs![General Ledger Department], rs![Work Center], rs![Pay Period Ending Date], rs!Hours, rs!Amount, rs!Week, rs![Pay Type Code], rs![Pay Type Description], rs![File Number], rs!Name, rs![HOURLY SALARY], rs![FULL TIME_PART TIME], rs!ACTIVE_INACTIVE, rs![HOURLY RATE],1/1/2017,rs!Processed )"
 Debug.Print (strsql)
  rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End With

Open in new window

0
 
aikimarkCommented:
Is "Connection String" the actual string literal you are using in your tests?

Although you set the value for strsql, you aren't using it in an EXECUTE method for the connection object.

In order to have multiple rows inserted, you need to use the VALUES format of the SQL insert statement that Dale Fye recommended in his comment.
0
 
Dale FyeCommented:
couple of issues here.

1.  Your SQL string is not formatted properly.  When you build your SQL dynamically, you cannot include the references to your recordset objects within quotes, they must be outside of the quotes, so your SQL line should look something like:

strsql = "INSERT INTO dbo_WorkTable ( ID,[L Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], " _
                                & "[General Ledger Cost Center], [General Ledger Department], [Work Center], [Pay Period Ending Date], " _
                                & "Hours, Amount, Week, [Pay Type Code], [Pay Type Description], [File Number], Name, " _
                                & "[HOURLY SALARY], [FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE],[Load Date],Processed) " _
       & "VALUES( " rs!ID & ", " & rs![L Batch ID] & "," & rs![Pay Group] & ", " _
                & chr$(34) &  Replace(rs![Pay Group Description], chr$(34), chr$(34) & chr$(34)) & chr$(34) & ", " _
                & rs![General Ledger Account] ", " & rs![General Ledger Cost Center] & ", " & rs![General Ledger Department] & ", " _
                & rs![Work Center] & ", #" & rs![Pay Period Ending Date] & "#, " & rs!Hours & ", " & rs!Amount & ", " & rs!Week & ", " _
                & rs![Pay Type Code] & ", " _
                & chr$(34) & Replace(rs![Pay Type Description], , chr$(34), chr$(34) & chr$(34)) & chr$(34) & ", " _
                & rs![File Number] & ", " _
                & chr$(34) & rs!Name & chr$(34) & ", " & rs![HOURLY SALARY] & ", " & rs![FULL TIME_PART TIME] & ", " _
                & rs!ACTIVE_INACTIVE & ", " & rs![HOURLY RATE] & ",#1/1/2017#, " & rs!Processed & ")"

Open in new window

Notice that for all of the fields that are obviously text, I placed a quote (chr$(34)) before and after the value.  And for several of those fields, I also used the Replace( ) function to ensure that any quotes (") that appear within the string are replaced with double quotes (""), which is necessary for Jet to properly format the string.

2.  The second point is that you don't have an EXECUTE Method in that loop, so after the debug.print command, I would add a line:

db.Execute strsql, dbfailonerror

Personally, I would add that line of code to your procedure and then remark it out until you are confident that the SQL string is formatted properly, which you can determine by adding a break point after the debug.print line.  The thing I'm concerned with with this method of processing is that some of those values in the recordset could be NULL.  Lets take a simple example and assume that rs!SomeField = NULL. When you build the SQL String, this would look like:

strSQL = strSQL & ", " & rs!SomeField & ", "

but if rs!SomeField is NULL then this would actually look like the following when printed:  ,,

Note that there is nothing (NULL) between the two commas.  So if there is a possibility that a value in the recordset could be NULL, then you need to use a syntax that looks something like:

strSQL = strSQL & ", " & NZ(rs!SomeField, "NULL") & ", "

Which when printed would look like: , NULL,

However, it looks like you are still attempting to do this insert from within Access, rather than creating a Pass-thru query, and allowing SQL Server to do the actual work.  This means that it is likely that you are not going to see much improvement in the amount of time it takes to do this insert process.
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Dale Fye i think this is the code template that would be best for my case, how can i create a passthrough query with a local table in it or record set i am not able to manipulate it.

dim qd as querydef
dim rs as recordset

set qd = currentdb.QueryDefs("<passthrough query name>") ---> how do i create a passthrough query without the local table in it is my question??

set rs = currentdb.openrecordset("SELECT * FROM WorkTable")

rs.movefirst
WHILE not rs.eof
qd.SQL = "INSERT INTO X (field1, fiel2) VALUES ("&rs!field1&", "&rs!field2&")"
currenntdb.execute ("<passthrough query name>") ----> is this the same query as above??
rs.movenext
wend

Open in new window

0
 
Dale FyeCommented:
I would start out by creating a pass-through query to retrieve (SELECT) records which already exist in the table you will eventually want to modify.  Are you familiar with pass-through queries?

1.  Create a new query, design view.
2.  Right click in the grey section at the top of the query design form, and select "SQL Specific" and then "Pass-Through".  This will put you in the SQL view
3.  Type:

SELECT TOP 10 * FROM dbo_WorkTable

4.  If you already have a linked table in your database, go to the immediate window and type:

?currentdb.Tabledefs("dbo_WorkTable").Connect

This should give you the connection string that you are currently using for your linked tables, which should also work for your passthrough query.  It should look something like:

ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=SERVERNAME;Trusted_Connection=Yes;APP=SSMA;DATABASE=DatabaseName

Copy that string.

5.  Return to your pass-through query, and then click the "Property Sheet" option on the Design tab of the ribbon.  This will display the properties window.  Paste the string you created into the ODBC Connect Str property of the dialog box.  Then make sure the Returns Records property reads "Yes".

Then run the query.  If that works, we can move on to the query which will write records to that table.
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Dale Fye  yes that worked i created a passthrough query called Select Worktable in which i added this code:
SELECT TOP 10 * FROM dbo.WorkTable

Open in new window


then did the immediate window code to get the connection string pasted in the appropriate location and then made sure return records was set to true.

i run the query and it is return a blank table that is correct since there is not data in this sql table. now how do i pass the local table data into this sql table sir??
0
 
Dale FyeCommented:
So now you need to change the SQL of that query to an append query.

But before you do that, did you modify your code so that your SQL string is constructed properly, as mentioned in my post above?  
Did you:
1. make sure that all text fields are wrapped in single quotes (since you are using a pass-thru), text fields should be wrapped in single quotes, since that is what SQL Server uses
2.  make sure that dates are properly formatted, they should also be wrapped in single quotes, and should be in the format 'yyyy-mm-dd'
3. account for the possibility of NULL values as I mentioned toward the end of that comment?
dim qd as querydef
dim rs as recordset

set qd = currentdb.QueryDefs("<passthrough query name>")

set rs = currentdb.openrecordset("SELECT * FROM WorkTable")
rs.movefirst                             '<=You don't really need this line
Do WHILE not rs.eof
    qd.SQL = "INSERT INTO X (field1, fiel2) VALUES ("&rs!field1&", "&rs!field2&")"  '<=This would be the query I defined above

'Insert the following line during testing, so that you can ensure that the data is formatted properly
    debug.print qd.SQL
'Make sure you use the dbFailOnError option in the execute statement and that you have error handling.
    qd.execute dbfailonerror
    rs.movenext
Loop

Open in new window


4.  Lastly, make sure you have a good error handler.  Because you are working with SQL Server, you may get more than one error message returned if your SQL string is not formatted correctly, so use code that loops through the errors collection to display your error messages:

ProcExit:
    on error resume next
    set qd = nothing
    rs.close
    set rs = nothing
    Exit Sub

Proc_Error:
    msgbox "Errors encountered, check your immediate window for error explanation."
    for intLoop = 0 to Errors.count - 1
        debug.print Errors(intLoop).Number, Errors(intLoop).Description
    Next
    Resume ProcExit:

Open in new window

0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Dale Fye i have implemented all of the above like you mentioned but there seems to be an syntax for the insert query, how do i fix this issue?

error-acess.PNG
0
 
aikimarkCommented:
You are missing a concatenation operator, &, between the string literal and the recordset variable.
0
 
Dale FyeCommented:
Needs to be an & between the " and rs!ID

& "Values (" & rs!ID &
0
 
Dale FyeCommented:
BTW, since you are going to do this as a Pass-Through, you need to replace all references to:

chr$(34)

which is a double quote, with:

chr$(39)

which is a single quote.

I actually have a function which I use to encapsulate values and to properly handle potential Null values.  The function looks like:
Public Function Quotes(QuoteWhat as Variant, Optional QuoteWith as string = """") as string

    If IsNull(QuoteWhat) Then
         Quotes = "NULL"
    Else
         Quotes = QuoteWith & Replace(QuoteWhat, QuoteWith, QuoteWith & QuoteWith) & QuoteWith
    End if

End Function

Open in new window

Using this function your SQL String would look something like:

strsql = "INSERT INTO dbo_WorkTable ( ID,[L Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], " _
                                & "[General Ledger Cost Center], [General Ledger Department], [Work Center], [Pay Period Ending Date], " _
                                & "Hours, Amount, Week, [Pay Type Code], [Pay Type Description], [File Number], Name, " _
                                & "[HOURLY SALARY], [FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE],[Load Date],Processed) " _
       & "VALUES( " rs!ID & ", " _
               & rs![L Batch ID] & "," _
               & Quotes(rs![Pay Group], "") & ", " _
               & Quotes(rs![Pay Group Description], "'") & ", " _
               & Quotes(rs![General Ledger Account], "'") & ", " _
               & Quotes(rs![General Ledger Cost Center], "") & ", " _
               & Quotes(rs![General Ledger Department], "") & ", " _
               & Quotes(rs![Work Center], "") & ", " _
               & Quotes(Format(rs![Pay Period Ending Date], "yyyy-mm-dd"), "'") & "," _
               & quotes(rs!Hours, "") & ", " _
               & quotes(rs!Amount, "") & ", " _
               & quotes(rs!Week, "") & ", " _
               & Quotes(rs![Pay Type Code], "'") & ", " _
               & Quotes(rs![Pay Type Description], "'") & ", " _
               & Quotes(rs![File Number], "") & ", " _
               & Quotes(rs!Name, "'") & ", " _
               & Quotes(rs![HOURLY SALARY], "") & ", " _
               & Quotes(rs![FULL TIME_PART TIME], "'") & ", " _
               & Quotes(rs!ACTIVE_INACTIVE, "") & ", " _
               & Quotes(rs![HOURLY RATE], "") & ", '2017-01-01', " _
               & Quotes(rs!Processed, "") & ")"

Open in new window

The advantage of this syntax is that if the value of the field in the recordset is NULL then the Quotes function will return a string value which reads "NULL", but if there is actually a value in the recordset field, then it will encapsulate it in the value which is the second argument of the function.  Notice that:
1.  Any fields which appear to be text have the 2nd argument as a single quote wrapped by double quotes ("'"),
2.  Any field which appears to be numeric has two double quotes (with nothing in between).  

Since I don't know the datatype of each of these fields, I may have some of those configured wrong.
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Dale Fye first let me start of by saying thank you for all your help, i did everything you mentioned above i am getting the error run-time error 3065 cannot execute a select query on line  qd.Execute dbFailOnError.
0
 
Dale FyeCommented:
and what does the SQL string look like when you get that error?  none of the code I provided has a SELECT statement in it, so I'm wondering where this got mixed up.  Can you please provide the exact code you are using, and then provide a copy of the SQL statement which you can either print out in the immediate window when the error occurs, or in a statement:

debug.print strSQL
or maybe:

debug.print qd.SQL
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
Entire Code

Dim qd As QueryDef
Dim rs As Recordset
Dim sqlStr As String

sqlStr = "SELECT * FROM WorkTable"
Set qd = CurrentDb.QueryDefs("Select WorkTable")
Set rs = CurrentDb.OpenRecordset(sqlStr)
With rs

Do While Not rs.EOF
qd.sql = "INSERT INTO dbo_WorkTable ( ID,[L Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], " _
                                & "[General Ledger Cost Center], [General Ledger Department], [Work Center], [Pay Period Ending Date], " _
                                & "Hours, Amount, Week, [Pay Type Code], [Pay Type Description], [File Number], Name, " _
                                & "[HOURLY SALARY], [FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE],[Load Date],Processed) " _
       & "VALUES( " & rs!ID & ", " _
               & rs![L Batch ID] & "," _
               & Quotes(rs![Pay Group], "") & ", " _
               & Quotes(rs![Pay Group Description], "'") & ", " _
               & Quotes(rs![General Ledger Account], "'") & ", " _
               & Quotes(rs![General Ledger Cost Center], "") & ", " _
               & Quotes(rs![General Ledger Department], "") & ", " _
               & Quotes(rs![Work Center], "") & ", " _
               & Quotes(Format(rs![Pay Period Ending Date], "yyyy-mm-dd"), "'") & "," _
               & Quotes(rs!Hours, "") & ", " _
               & Quotes(rs!Amount, "") & ", " _
               & Quotes(rs!Week, "") & ", " _
               & Quotes(rs![Pay Type Code], "'") & ", " _
               & Quotes(rs![Pay Type Description], "'") & ", " _
               & Quotes(rs![File Number], "") & ", " _
               & Quotes(rs!Name, "'") & ", " _
               & Quotes(rs![HOURLY SALARY], "") & ", " _
               & Quotes(rs![FULL TIME_PART TIME], "'") & ", " _
               & Quotes(rs!ACTIVE_INACTIVE, "") & ", " _
               & Quotes(rs![HOURLY RATE], "") & ", '2017-01-01', " _
               & Quotes(rs!Processed, "") & ")"


   Debug.Print qd.sql
   qd.ReturnsRecords = True
     qd.Execute dbFailOnError  ----------> this line throws the cannot execute select query i think it might be the fact that its a insert into statement but the return records property is set to true.  when i changed that to false i am getting ODBC--call failed error 3146
    rs.MoveNext
Loop
ProcExit:
    On Error Resume Next
    Set qd = Nothing
    rs.Close
    Set rs = Nothing
    Exit Sub

Proc_Error:
    MsgBox "Errors encountered, check your immediate window for error explanation."
    For intLoop = 0 To Errors.Count - 1
        Debug.Print Errors(intLoop).Number, Errors(intLoop).Description
    Next
    Resume ProcExit:
End With
End Sub

Open in new window



debug.print strSQL
sqlStr = "SELECT * FROM WorkTable"

debug.print qd.sql
INSERT INTO dbo_WorkTable ( ID,[L Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], [General Ledger Cost Center], [General Ledger Department], [Work Center], [Pay Period Ending Date], Hours, Amount, Week, [Pay Type Code], [Pay Type Description], [File Number], Name, [HOURLY SALARY], [FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE],[Load Date],Processed) VALUES( {00001813-96D5-4444-AB33-63ADEDD368E2}, 10,X2U, 'KANSAS CITY', '224200', , , 08405, '2016-12-31',, -63.15, 51, 'T.LOC', 'EE LOCAL TAX', 710990, 'Ranga,Arvin M', S, 'RF', A, 41.28833506, '2017-01-01', False)

Open in new window

0
 
Dale FyeCommented:
A couple of issues.
1.  You are correct, returns Records should be set to False for a passthrough action query.  If you want to return a value to your calling routine, you would need to modify this and pass all of these values to a stored procedure, which could return the status of the insert operation.

2.  If you examine the value of qd.SQL, you will see that there are a number of fields which are obviously text, which do not have single quotes surrounding them.  
a.  I have not worked with guids, so I'm not certain whether that first column will work properly,
b.  the 3rd, is obviously a string and should have single quotes around it.  
c.  Columns 6 and 7 contain nothing between the commas.  You must have something in there, either NULL or a value, but the way they are handled in the code looks like:
               & Quotes(rs![General Ledger Cost Center], "") & ", " _
               & Quotes(rs![General Ledger Department], "") & ", " _

Open in new window

so if you implemented my Quotes function as described above, then the values of these two fields is NOT null, but an empty string.  Confirm whether those fields are text fields, and if so, change the code to look like:
               & Quotes(rs![General Ledger Cost Center], "'") & ", " _
               & Quotes(rs![General Ledger Department], "'") & ", " _

Open in new window

Which has a single quote, encapsulated by double quotes as the 2nd argument of the function call.

d.  the 8th column starts with a zero, so this is probably also a string and should be surrounded by single quotes.  There are others "S", and "A" which should have but don't currently have single quotes.Figure out which ones are strings, and make sure that you use the correct syntax in the SQL build that will wrap those fields

3.  You need to turn on Error Handling in order for the error messages to display properly in your immediate window.  Modify your code as follows:
Dim sqlStr As String
on error goto Proc_Error
sqlStr = "SELECT * FROM WorkTable"

Open in new window

This way, when you get an error, it will print out all of the error messages in the Immediate window.
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Dale Fye this is the new error i am catching after i changed the return records to false:

INSERT INTO dbo_WorkTable ( ID,[L Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], [General Ledger Cost Center], [General Ledger Department], [Work Center], [Pay Period Ending Date], Hours, Amount, Week, [Pay Type Code], [Pay Type Description], [File Number], Name, [HOURLY SALARY], [FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE],[Load Date],Processed) VALUES( {00001813-96D5-4444-AB33-63ADEDD368E2}, 10,'X2U', 'KANSAS CITY', '224200', , , '08405', '2016-12-31',, -63.15, 51, 'T.LOC', 'EE LOCAL TAX', 710990, 'Ranga,Arvin M', 'S', 'RF', 'A', 41.28833506, '2017-01-01', False)
 0            [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
 3146         ODBC--call failed.
, '2017-01-01', False)

Open in new window

0
 
Dale FyeCommented:
You still have not resolved the issue with the blanks between commas:
                                                                                                                                                             
VALUES( {00001813-96D5-4444-AB33-63ADEDD368E2}, 10,'X2U', 'KANSAS CITY', '224200', , , '08405', '2016-12-31',, -63.15, 51, 'T.LOC', 'EE LOCAL TAX', 710990, 'Ranga,Arvin M', 'S', 'RF', 'A', 41.28833506, '2017-01-01', False)

Open in new window


First thing I would do is remove the quid from the INSERT and the VALUES portions of the query, to make sure the rest of the query works properly, without the quid..

Then I would fix the missing values between the commas (2nd and 3rd fields following 'Kansas City' and the column immediately to the right of the date '2016-12-31').  I made suggestions to fix that in my previous post.
0
 
aikimarkCommented:
Also, comment or remove this line:
qd.ReturnsRecords = True

Open in new window

0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Dale Fye i fixed all the comma values, and removed the guid for now and it is still throwing a syntax error:


INSERT INTO dbo_WorkTable ([L Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], [General Ledger Cost Center], [General Ledger Department], [Work Center], [Pay Period Ending Date], Hours, Amount, Week, [Pay Type Code], [Pay Type Description], [File Number], Name, [HOURLY SALARY], [FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE],[Load Date],Processed) VALUES( 10,'X2U', 'KANSAS CITY', '223200', '', '', '08402', '2016-12-31','', -62.15, 51, 'T.LOC', 'EE LOCAL TAX', 710890, 'Ranga,Arvin M', 'S', 'RF', 'A', 40.28833506, '2017-01-01', False)
 207          [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'False'.
 3146         ODBC--call failed.
, 'EE LOCAL TAX', 710890, 'BLACKMON,GINGER M', 'S', 'RF', 'A', 40.28833506, '2017-01-01', False)
 102          [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ','.
 3146         ODBC--call failed.
'', '', '08402', '2016-12-31',, -62.15, 51, 'T.LOC', 'EE LOCAL TAX', 710890, 'BLACKMON,GINGER M', 'S', 'RF', 'A', 40.28833506, '2017-01-01', False)
 0            [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
 3146         ODBC--call failed.
, '2017-01-01', False)

Open in new window


datatype on sql server
0
 
Dale FyeCommented:
INSERT INTO dbo_WorkTable ([L Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], [General Ledger Cost Center], [General Ledger Department], [Work Center], [Pay Period Ending Date], Hours, Amount, Week, [Pay Type Code], [Pay Type Description], [File Number], Name, [HOURLY SALARY], [FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE],[Load Date],Processed)
VALUES( 10,'X2U', 'KANSAS CITY', '223200', '', '', '08402', '2016-12-31','', -62.15, 51, 'T.LOC', 'EE LOCAL TAX', 710890, 'Ranga,Arvin M', 'S', 'RF', 'A', 40.28833506, '2017-01-01', False)

This is probably not causing the problem, but why is the [Pay Period Ending Date] field described as nvarchar(50) instead of date, also Hours.

Your table defines the Amount() column as nvarchar(50), but the value shown in your SQL statement for that field is -62.15, so you either need to change the field type in the SQL database or wrap that -62.15 value in single quotes.

FileNumber is also defined as nvarchar(50), but there are no quotes around that value in the SQL string.

Hourly Rate is also defined as nvarchar(50) but the value in your SQL string is numeric

You also might want to change the final line from

               & Quotes(rs!Processed, "") & ")"

to:

               & Quotes(iif(rs!Processed, 0, 1), "") & ")"

This ensures that SQL Server is going to interpret the [Processed] field as 0 or 1.
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Dale Fye finally i have it loading into the Sql Database YAY!!!! but the application goes into a (Not Responding) Mode but the insert is successfully taking place in the database. the biggest issue in the sql syntax was that the table i am trying to insert into was dbo_Worktable but in SQl Server it is dbo.WorkTable so i fixed that an it started to insert. i just stuck to the datatype nvarchar and decided to bring the value in with quotes instead of the appropriate data type because there was issue when i was loading the load table initially. so how can i keep the application from going into the not responding  mode?
0
 
PatHartmanCommented:
the biggest issue in the sql syntax was that the table i am trying to insert into was dbo_Worktable but in SQl Server it is dbo.WorkTable so i fixed that an it started to insert.
This isn't a problem at all.  It is simply one of the differences between SQL Server and Access syntax.  Access doesn't allow dots in any object names so it substitutes an underscore for any character it doesn't like.  In fact, if you name your columns Sale Amount % and Sale Amount $, you'll see what happens to Access because it will attempt to reference them both as Sale_Amount__

If you load date data as a string, it will act like a string so you need to fix that.  Old ODBC drivers, which are what most applications are using, only support the SQL Server DateTime data type.  They do NOT support any other type.  If you need a different type, you MUST upgrade yourself AND ALL YOUR USERS to a more current ODBC driver.
0
 
Dale FyeCommented:
ARe you doing this in the loop?
If so, add a line inside the loop to increment a counter and print it to the debug window.  I'm guessing this has to do with the fact that you have a lot of records to write, and the loop is just bogging Access down.  YOu could also insert a DoEvents line in side the loop, which will free things up a bit.

intCounter = intCounter + 1
debug.print intCounter
doEvents

Open in new window


You also might want to just modify sqlSTR to something like for a test.

sqlStr = "SELECT top 10 * FROM WorkTable"


Have you modified the code back to the original so that it is now inserting the GUID (ID) field also?
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Dale Fye everything works fine but according to your estimate how long do you think it should take for 59000 records to insert using our newly built code??
0
 
aikimarkCommented:
You are still inserting one-at-a-time.  I wouldn't expect any performance boost.
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@aikimark but i followed everything what Dale Fye suggested infact this ruins the title of my question in the first place. so is this my only option then, you are right i am not seeing any performance boost. how every my stop watch shows 30 mins and the amount of data inserted is 31000 so its a 1000 more. i have read of people achieving faster inserts i am not sure where i am messing things up :(
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@aikimark all i can tell you even though it takes 60 minutes to insert 59k data into sql server, @Dale Fye has helped me cut down the times it takes by half.  it was only able to insert 563 records per minute now it inserts 1000. i would love to see it go a little but faster however i will take a look at the link you gave me.
0
 
Dale FyeCommented:
aikimark's point was that you can string the inserts together, like:
INSERT INTO Table (field1, field2)
VALUES
('string1','string2'),
('stringA','stringB'),
('StringC', 'StringD')

To do this, you would need to create an inner loop as well, something like:
Dim strInsert as string
Dim strValuea as String
Dim intLoopCounter as integer

strInsert = "INSERT INTO dbo_WorkTable ([L Batch ID], [Pay Group], [Pay Group Description], " _
                 & "[General Ledger Account], [General Ledger Cost Center], [General Ledger Department], " _
                 & "[Work Center], [Pay Period Ending Date], Hours, Amount, Week, [Pay Type Code], " _
                 & "[Pay Type Description], [File Number], Name, [HOURLY SALARY], [FULL TIME_PART TIME], " _
                 & "ACTIVE_INACTIVE, [HOURLY RATE],[Load Date],Processed) "
While not rs.eof
    strValues = ""
    intLoopCounter = 0
   While intLoop < 10
       strValues = strValues & ", " _
                        & "VALUES( 
'Insert your Values code here
                        & ")"
        rs.movenext
    Wend
    qd.SQL = strInsert & strValues
    qd.Execute  dbFailonerror
Wend

Open in new window

This would insert 10 records at a time into the SQL table and should speed up the process.

The alternative to this would be to write the data to a CSV file (this could be very fast) and then read the data into SQL Server with a BulkInsert statement.
0
 
aikimarkCommented:
Be aware that any single SQL statement is limited to 64K bytes.
0
 
aikimarkCommented:
If that still isn't fast enough you can also block your inserts inside transactions.  So, you might begin a transaction, send 10 Insert statements that each insert 10 rows of data, and then commit the transaction.  This further minimizes the commits to 1 commit per 100 rows versus 1 commit per 10 rows.  What your current code does is 1 commit per row.
0
 
Dale FyeCommented:
Which is partially why I limited my example to 10 records at at time, but with the 64K limit and the limited amount of data you appear to have in each record, you could probably do at least 50 at a time.  

Just three minor changes to the code I provided earlier
1.  you would need to move the "VALUES" as indicated below
2.  when concatenating strValues to strInsert, you would need to strip out the leading comma using the MID ( ) function.
3.  You would need to increment intLoopCounter at the bottom of the inner loop.

Dim strInsert as string
Dim strValuea as String
Dim intLoopCounter as integer

strInsert = "INSERT INTO dbo_WorkTable ([L Batch ID], [Pay Group], [Pay Group Description], " _
                 & "[General Ledger Account], [General Ledger Cost Center], [General Ledger Department], " _
                 & "[Work Center], [Pay Period Ending Date], Hours, Amount, Week, [Pay Type Code], " _
                 & "[Pay Type Description], [File Number], Name, [HOURLY SALARY], [FULL TIME_PART TIME], " _
                 & "ACTIVE_INACTIVE, [HOURLY RATE],[Load Date],Processed) " _
                 & "VALUES "
While not rs.eof
    strValues = ""
    intLoopCounter = 0
   While intLoopCounter < 10
       strValues = strValues & ", " _
                        & "("
 'Insert your Values code here
                        & ")"
        rs.movenext
        intLoopCounter = intLoopCounter + 1
    Wend
    qd.SQL = strInsert & Mid(strValues, 2)
    qd.Execute  dbFailonerror
Wend

Open in new window

0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Dale Fye i am getting the following error:
 102          [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '{00001813-96D5-4444-AB33-63ADEDD368E2}'.
 3146         ODBC--call failed.

but when i do a ?qd.sql i get the following for values:
('{00001813-96D5-4444-AB33-63ADEDD368E2}', 10,'X2U', 'KANSAS CITY', '223200', '', '', '08402', '2016-12-31','', '-62.15', 51, 'T.LOC', 'EE LOCAL TAX', '710890', 'Edward,Hayes M', 'S', 'RF', 'A', '41.28833506', '2017-01-01', 0), ('{00008E64-C916-41EC-B174-93A5F4872F72}', 10,'X2W', 'KANSAS CITY', '100351', '', '', '80103', '2016-12-17','', '-396.72', 51, 'D.  X', 'CHECKING 1', '085832', 'YOUNG,John K', 'H', 'RP', 'A', '16', '2017-01-01', 0),

The syntax seems fine for me this is all the code:

Dim strInsert As String
Dim strValuea As String
Dim intLoopCounter As Integer
Dim qd As QueryDef
Dim rs As Recordset
Dim sqlStr As String
On Error GoTo Proc_Error
sqlStr = "SELECT * FROM WorkTable"
Set qd = CurrentDb.QueryDefs("Select WorkTable")
Set rs = CurrentDb.OpenRecordset(sqlStr)
With rs


qd.sql = "INSERT INTO dbo.WorkTable (ID,[L Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], " _
                                & "[General Ledger Cost Center], [General Ledger Department], [Work Center], [Pay Period Ending Date], " _
                                & "Hours, Amount, Week, [Pay Type Code], [Pay Type Description], [File Number], Name, " _
                                & "[HOURLY SALARY], [FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE],[Load Date],Processed) " _
                                & "VALUES "
  While Not rs.EOF
       
    trValues = ""
    intLoopCounter = 0
   While intLoopCounter < 10
       strValues = strValues & ", " _
       & "(" & Quotes(rs!ID, "'") & ", " _
       & rs![L Batch ID] & "," _
               & Quotes(rs![Pay Group], "'") & ", " _
               & Quotes(rs![Pay Group Description], "'") & ", " _
               & Quotes(rs![General Ledger Account], "'") & ", " _
               & Quotes(rs![General Ledger Cost Center], "'") & ", " _
               & Quotes(rs![General Ledger Department], "'") & ", " _
               & Quotes(rs![Work Center], "'") & ", " _
               & Quotes(Format(rs![Pay Period Ending Date], "yyyy-mm-dd"), "'") & "," _
               & Quotes(rs!Hours, "'") & ", " _
               & Quotes(rs!Amount, "'") & ", " _
               & Quotes(rs!Week, "") & ", " _
               & Quotes(rs![Pay Type Code], "'") & ", " _
               & Quotes(rs![Pay Type Description], "'") & ", " _
               & Quotes(rs![File Number], "'") & ", " _
               & Quotes(rs!Name, "'") & ", " _
               & Quotes(rs![HOURLY SALARY], "'") & ", " _
               & Quotes(rs![FULL TIME_PART TIME], "'") & ", " _
               & Quotes(rs!ACTIVE_INACTIVE, "'") & ", " _
               & Quotes(rs![HOURLY RATE], "'") & ", '2017-01-01', " _
               & Quotes(rs![Processed], "") & ")"
Debug.Print qd.sql
 rs.MoveNext
        intLoopCounter = intLoopCounter + 1
    Wend
    qd.sql = strInsert & Mid(strValues, 2)
    qd.Execute dbFailOnError
   
Wend
  '## Debug.Print qd.sql
  '## qd.ReturnsRecords = False
   '##  qd.Execute dbFailOnError
   '## rs.MoveNext
   '## intCounter = intCounter + 1
'## Debug.Print intCounter
'## DoEvents
'## Loop
ProcExit:
    On Error Resume Next
    Set qd = Nothing
    rs.Close
    Set rs = Nothing
    Exit Sub

Proc_Error:
    MsgBox "Errors encountered, check your immediate window for error explanation."
    For intLoop = 0 To Errors.Count - 1
        Debug.Print Errors(intLoop).Number, Errors(intLoop).Description
    Next
    Resume ProcExit:
End With

Open in new window


what am i missing here Dale??
0
 
aikimarkCommented:
You are showing a trailing comma character.
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@ aikimark ('{00001813-96D5-4444-AB33-63ADEDD368E2}', 10,'X2U', 'KANSAS CITY', '223200', '', '', '08402', '2016-12-31','', '-62.15', 51, 'T.LOC', 'EE LOCAL TAX', '710890', 'Edward,Hayes M', 'S', 'RF', 'A', '41.28833506', '2017-01-01', 0),<--- This comma separates the values between values for group insert ('{00008E64-C916-41EC-B174-93A5F4872F72}', 10,'X2W', 'KANSAS CITY', '100351', '', '', '80103', '2016-12-17','', '-396.72', 51, 'D.  X', 'CHECKING 1', '085832', 'YOUNG,John K', 'H', 'RP', 'A', '16', '2017-01-01', 0),<---- this comma was just a copy mistake i have 10 such records i wanted to copy just a few here and ended up copying with a comma.

any suggestions??
0
 
aikimarkCommented:
Remove the curly braces from the GUID string values.
Example:
('00001813-96D5-4444-AB33-63ADEDD368E2', 10,'X2U', 'KANSAS CITY', '223200', '', '', '08402', '2016-12-31','', '-62.15', 51, 'T.LOC', 'EE LOCAL TAX', '710890', 'Edward,Hayes M', 'S', 'RF', 'A', '41.28833506', '2017-01-01', 0),
('00008E64-C916-41EC-B174-93A5F4872F72', 10,'X2W', 'KANSAS CITY', '100351', '', '', '80103', '2016-12-17','', '-396.72', 51, 'D.  X', 'CHECKING 1', '085832', 'YOUNG,John K', 'H', 'RP', 'A', '16', '2017-01-01', 0)

Open in new window

0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@aikimark that is not the issue i am able to insert the record into sql server with the curly braces from sql server management studio.

Capturefdsfsdfs.PNG
0
 
Dale FyeCommented:
Hey, guys, I'm back.

Aravind,

line 21 in your code says:

trvalues = ""

not

strValues = ""

That is the first thing I would check.

Then, change line 23 to:

   While intLoopCounter < 2

and put a breakpoint on the line which executes the query;

qd.Execute

So you can examine the SQL in the immediate window before attempting to write those two new records to SQL Server.  This will also give you the ability to cancel execution of the loop if an error occurs.

Then, I would move the debug.print line down between the two lines where you define qd.sql and execute the query.
0
 
aikimarkCommented:
@Aravind

Please add Option Explicit statement up in the general declarations section of your code.
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Dale Fye getting this error:
 102          [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '{00001813-96D5-4444-AB33-63ADEDD368E2}'
 3146         ODBC--call failed.

debug.print qd.sql:

INSERT INTO dbo.WorkTable (ID,[L Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], [General Ledger Cost Center], [General Ledger Department], [Work Center], [Pay Period Ending Date], Hours, Amount, Week, [Pay Type Code], [Pay Type Description], [File Number], Name, [HOURLY SALARY], [FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE],[Load Date],Processed) VALUES 
'{00001813-96D5-4444-AB33-63ADEDD368E2}', 10,'X2U', 'Chicago CITY', '224300', '', '', '09502', '2016-12-31','', '-62.15', 51, 'T.LOA', 'EF LOCAL TAX', '721890', 'WHITEMON,GARLIC M', 'S', 'RF', 'A', '40.28833506', '2017-01-01', 0)('{00008E64-C916-41EC-B174-93A5F4872F72}', 10,'X2Y', 'Chicago CITY', '100432', '', '', '901203', '2016-12-17','', '-398.82', 51, 'D.  X', 'CHECKING 1', '085832', 'RAMBO,JOHN K', 'H', 'RP', 'A', '16', '2017-01-01', 0)

Open in new window

0
 
Dale FyeCommented:
Aravind,

What is the data type of the ID field in SQL Server?

You might try removing the "{" and "}" brackets and see if that works.

This did work properly before you added the ID field back into the process, right?
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Dale Fye i pasted this code in sql server and came to know that there is no "(" after values and no "," in between the different values.
0
 
Dale FyeCommented:
Not sure what you mean?  The general syntax for inserting multiple values at a time into SQL server is:

INSERT INTO tblWhatever (Field1)
Values (1), (2), (3)

or for multiple fields:

Insert into tblWhatever(NumberField, textField)
Values (1, 'a'), (2, 'b'), (3, 'c')
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Dale Fye yea if u look at the syntax in the above code i am missing the begining "(" and missing a "," in between ()().
0
 
Dale FyeCommented:
can you please repost your current code.  I believe the last code example you provided contains an error on line 21, but I'd like to review your latest code.
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@ Dale Fye:

Dim strInsert As String
Dim strValuea As String
Dim intLoopCounter As Integer
Dim qd As QueryDef
Dim rs As Recordset
Dim sqlStr As String
On Error GoTo Proc_Error
sqlStr = "SELECT * FROM WorkTable"
Set qd = CurrentDb.QueryDefs("Select WorkTable")
Set rs = CurrentDb.OpenRecordset(sqlStr)
With rs


qd.sql = "INSERT INTO dbo.WorkTable (ID,[L Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], " _
                                & "[General Ledger Cost Center], [General Ledger Department], [Work Center], [Pay Period Ending Date], " _
                                & "Hours, Amount, Week, [Pay Type Code], [Pay Type Description], [File Number], Name, " _
                                & "[HOURLY SALARY], [FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE],[Load Date],Processed) " _
                                & "VALUES "
  While Not rs.EOF
       
    strValues = ""
    intLoopCounter = 0
   While intLoopCounter < 2
       strValues = strValues & ", " _
       & "(" & Quotes(rs!ID, "'") & ", " _
       & rs![L Batch ID] & "," _
               & Quotes(rs![Pay Group], "'") & ", " _
               & Quotes(rs![Pay Group Description], "'") & ", " _
               & Quotes(rs![General Ledger Account], "'") & ", " _
               & Quotes(rs![General Ledger Cost Center], "'") & ", " _
               & Quotes(rs![General Ledger Department], "'") & ", " _
               & Quotes(rs![Work Center], "'") & ", " _
               & Quotes(Format(rs![Pay Period Ending Date], "yyyy-mm-dd"), "'") & "," _
               & Quotes(rs!Hours, "'") & ", " _
               & Quotes(rs!Amount, "'") & ", " _
               & Quotes(rs!Week, "") & ", " _
               & Quotes(rs![Pay Type Code], "'") & ", " _
               & Quotes(rs![Pay Type Description], "'") & ", " _
               & Quotes(rs![File Number], "'") & ", " _
               & Quotes(rs!Name, "'") & ", " _
               & Quotes(rs![HOURLY SALARY], "'") & ", " _
               & Quotes(rs![FULL TIME_PART TIME], "'") & ", " _
               & Quotes(rs!ACTIVE_INACTIVE, "'") & ", " _
               & Quotes(rs![HOURLY RATE], "'") & ", '2017-01-01', " _
               & Quotes(rs![Processed], "") & ")"
Debug.Print qd.sql
 rs.MoveNext
        intLoopCounter = intLoopCounter + 1
    Wend
    qd.sql = strInsert & Mid(strValues, 2)
    Debug.Print qd.sql
    qd.Execute dbFailOnError

Open in new window

0
 
Dale FyeCommented:
That isn't quite all of it, because that doesn't include the 2nd Wend.  Please get it all.
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Dale Fye
Dim strInsert As String
Dim strValuea As String
Dim intLoopCounter As Integer
Dim qd As QueryDef
Dim rs As Recordset
Dim sqlStr As String
On Error GoTo Proc_Error
sqlStr = "SELECT * FROM WorkTable"
Set qd = CurrentDb.QueryDefs("Select WorkTable")
Set rs = CurrentDb.OpenRecordset(sqlStr)
With rs


qd.sql = "INSERT INTO dbo.WorkTable (ID,[L Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], " _
                                & "[General Ledger Cost Center], [General Ledger Department], [Work Center], [Pay Period Ending Date], " _
                                & "Hours, Amount, Week, [Pay Type Code], [Pay Type Description], [File Number], Name, " _
                                & "[HOURLY SALARY], [FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE],[Load Date],Processed) " _
                                & "VALUES "
  While Not rs.EOF
       
    strValues = ""
    intLoopCounter = 0
   While intLoopCounter < 2
       strValues = strValues & ", " _
       & "(" & Quotes(rs!ID, "'") & ", " _
       & rs![L Batch ID] & "," _
               & Quotes(rs![Pay Group], "'") & ", " _
               & Quotes(rs![Pay Group Description], "'") & ", " _
               & Quotes(rs![General Ledger Account], "'") & ", " _
               & Quotes(rs![General Ledger Cost Center], "'") & ", " _
               & Quotes(rs![General Ledger Department], "'") & ", " _
               & Quotes(rs![Work Center], "'") & ", " _
               & Quotes(Format(rs![Pay Period Ending Date], "yyyy-mm-dd"), "'") & "," _
               & Quotes(rs!Hours, "'") & ", " _
               & Quotes(rs!Amount, "'") & ", " _
               & Quotes(rs!Week, "") & ", " _
               & Quotes(rs![Pay Type Code], "'") & ", " _
               & Quotes(rs![Pay Type Description], "'") & ", " _
               & Quotes(rs![File Number], "'") & ", " _
               & Quotes(rs!Name, "'") & ", " _
               & Quotes(rs![HOURLY SALARY], "'") & ", " _
               & Quotes(rs![FULL TIME_PART TIME], "'") & ", " _
               & Quotes(rs!ACTIVE_INACTIVE, "'") & ", " _
               & Quotes(rs![HOURLY RATE], "'") & ", '2017-01-01', " _
               & Quotes(rs![Processed], "") & ")"
Debug.Print qd.sql
 rs.MoveNext
        intLoopCounter = intLoopCounter + 1
    Wend
    qd.sql = strInsert & Mid(strValues, 2)
    Debug.Print qd.sql
    qd.Execute dbFailOnError
   
Wend
  '## Debug.Print qd.sql
  '## qd.ReturnsRecords = False
   '##  qd.Execute dbFailOnError
   '## rs.MoveNext
   '## intCounter = intCounter + 1
'## Debug.Print intCounter
'## DoEvents
'## Loop
ProcExit:
    On Error Resume Next
    Set qd = Nothing
    rs.Close
    Set rs = Nothing
    Exit Sub

Proc_Error:
    MsgBox "Errors encountered, check your immediate window for error explanation."
    For intLoop = 0 To Errors.Count - 1
        Debug.Print Errors(intLoop).Number, Errors(intLoop).Description
    Next
    Resume ProcExit:
End With

Open in new window

0
 
Dale FyeCommented:
First things first.  Move the End With line from where it is to the line just above ProcExit:

I've only modified a couple of lines here, and nothing which should really change anything, but lets try this.  Can you run this with a breakpoint on the qd.Execute line, copy the SQL that was printed to the immediate window and paste the entire SQL string here.  Also, copy that string over to SQL Server and attempt to run the query directly from SQL Server.
Set rs = CurrentDb.OpenRecordset(sqlStr)
With rs

   qd.sql = "INSERT INTO dbo.WorkTable (ID,[L Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], " _
          & "[General Ledger Cost Center], [General Ledger Department], [Work Center], [Pay Period Ending Date], " _
          & "Hours, Amount, Week, [Pay Type Code], [Pay Type Description], [File Number], Name, " _
          & "[HOURLY SALARY], [FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE],[Load Date],Processed) " _
          & "VALUES "
   While Not rs.EOF
       
      strValues = ""
      intLoopCounter = 0
 
      'now the inner loop which builds the insert values portion of the string  
      While intLoopCounter < 2 AND not rs.eof
         strValues = strValues & ", (" _
             & Quotes(rs!ID, "'") & ", " _
             & rs![L Batch ID] & "," _
             & Quotes(rs![Pay Group], "'") & ", " _
             & Quotes(rs![Pay Group Description], "'") & ", " _
             & Quotes(rs![General Ledger Account], "'") & ", " _
             & Quotes(rs![General Ledger Cost Center], "'") & ", " _
             & Quotes(rs![General Ledger Department], "'") & ", " _
             & Quotes(rs![Work Center], "'") & ", " _
             & Quotes(Format(rs![Pay Period Ending Date], "yyyy-mm-dd"), "'") & "," _
             & Quotes(rs!Hours, "'") & ", " _
             & Quotes(rs!Amount, "'") & ", " _
             & Quotes(rs!Week, "") & ", " _
             & Quotes(rs![Pay Type Code], "'") & ", " _
             & Quotes(rs![Pay Type Description], "'") & ", " _
             & Quotes(rs![File Number], "'") & ", " _
             & Quotes(rs!Name, "'") & ", " _
             & Quotes(rs![HOURLY SALARY], "'") & ", " _
             & Quotes(rs![FULL TIME_PART TIME], "'") & ", " _
             & Quotes(rs!ACTIVE_INACTIVE, "'") & ", " _
             & Quotes(rs![HOURLY RATE], "'") & ", '2017-01-01', " _
             & Quotes(rs![Processed], "") _
             & ")"
         rs.MoveNext
         intLoopCounter = intLoopCounter + 1
      Wend
      qd.sql = strInsert & Mid(strValues, 2)
Debug.Print qd.sql
      qd.Execute dbFailOnError
   
   Wend

Open in new window

0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Dale Fye this is what gets printed on the immediate window.

 102          [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '{00001813-96D5-4444-AB33-63ADEDD368E2}'.
 3146         ODBC--call failed.
 ('{00001813-96D5-4444-AB33-63ADEDD368E2}', 10,'X2U', 'KANSAS CITY', '223200', '', '', '08402', '2016-12-31','', '-62.15', 51, 'T.LOC', 'EE LOCAL TAX', '710890', 'BLACKMON,GINGER M', 'S', 'RF', 'A', '40.28833506', '2017-01-01', 0), ('{00008E64-C916-41EC-B174-93A5F4872F72}', 10,'X2W', 'KANSAS CITY', '100351', '', '', '80103', '2016-12-17','', '-396.72', 51, 'D.  X', 'CHECKING 1', '085832', 'YOUNG,KIMBERLY K', 'H', 'RP', 'A', '16', '2017-01-01', 0)

Open in new window


I pasted the values as such in sql server and it worked:
INSERT INTO dbo.WorkTable (ID,[L Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], [General Ledger Cost Center], [General Ledger Department], [Work Center], [Pay Period Ending Date], Hours, Amount, Week, [Pay Type Code], [Pay Type Description], [File Number], Name, [HOURLY SALARY], [FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE],[Load Date],Processed) VALUES 
('{00001813-96D5-4444-AB33-63ADEDD368E2}', 10,'X2U', 'KANSAS CITY', '223200', '', '', '08402', '2016-12-31','', '-62.15', 51, 'T.LOC', 'EE LOCAL TAX', '710890', 'BLACKMON,GINGER M', 'S', 'RF', 'A', '40.28833506', '2017-01-01', 0), ('{00008E64-C916-41EC-B174-93A5F4872F72}', 10,'X2W', 'KANSAS CITY', '100351', '', '', '80103', '2016-12-17','', '-396.72', 51, 'D.  X', 'CHECKING 1', '085832', 'YOUNG,KIMBERLY K', 'H', 'RP', 'A', '16', '2017-01-01', 0)

Open in new window

0
 
Dale FyeCommented:
next test:

Now copy this (I've removed the squiggly brackets) and see whether it will execute properly in SQL Server.
INSERT INTO dbo.WorkTable (ID,[L Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], [General Ledger Cost Center], [General Ledger Department], [Work Center], [Pay Period Ending Date], Hours, Amount, Week, [Pay Type Code], [Pay Type Description], [File Number], Name, [HOURLY SALARY], [FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE],[Load Date],Processed) VALUES
('00001813-96D5-4444-AB33-63ADEDD368E2', 10,'X2U', 'KANSAS CITY', '223200', '', '', '08402', '2016-12-31','', '-62.15', 51, 'T.LOC', 'EE LOCAL TAX', '710890', 'BLACKMON,GINGER M', 'S', 'RF', 'A', '40.28833506', '2017-01-01', 0), 
('00008E64-C916-41EC-B174-93A5F4872F72', 10,'X2W', 'KANSAS CITY', '100351', '', '', '80103', '2016-12-17','', '-396.72', 51, 'D.  X', 'CHECKING 1', '085832', 'YOUNG,KIMBERLY K', 'H', 'RP', 'A', '16', '2017-01-01', 0)

Open in new window

0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Dale Fye yes it executed correctly into sql server.
0
 
Dale FyeCommented:
then lets remark out this line

      qd.sql = strInsert & Mid(strValues, 2)

and replace it with

      qd.SQL = strInsert & Replace(Replace(Mid(strValues, 2), "{", ""), "}", "")

and see if that works.
0
 
Dale FyeCommented:
make sure that the SQL table doesn't have a unique index on that ID column.  If it does, then the insert will fail because you already have values in the table.
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Dale Fye :  102          [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '00001813-96D5-4444-AB33-63ADEDD368E2'.
 3146         ODBC--call failed.

i checked the ID column by inserting the same values multiple times and it executed dale so no unique index on that column
0
 
Dale FyeCommented:
I'm baffled.

what is the connection string for your pass-thru query?

What is the ReturnsRecords property of the query? (It should be No)

If you change the ReturnsRecords property to Yes, can you execute a SELECT query:

SELECT * FROM dbo.WorkTable
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Dale Fye: i double clicked on the select pass though query and i got this message
abcde.PNG
then i clicked on yes and got this:

abcdef.PNG
and this is my connection string dale :

ODBC;DSN=adpgl;Description=adpgl;APP=Microsoft Office 2010;DATABASE=AdpGL;TABLE=dbo.WorkTable


i have this next to the pass thorugh  query i dont know if that means anything:

abcdefg.PNG
0
 
Dale FyeCommented:
Can you do:

SELECT * FROM dbo.WorkTable

from within your querydef?  Does that work? (you will probably have to change the ReturnsRecords property to Yes).

If you attempt to edit the DSN connection you created, what SQL SERVER driver is selected?

What Version of SQL Server are you using?
0
 
Dale FyeCommented:
Can you open SQL Server, select table WorkTable, right click and select the to script the database to a new query and post the table definition code that gets generated.  I'd like to review the field types that you are using and how they are defined.
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Dale Fye this is the error i got when the return records
 3065         Cannot execute a select query. - > when set to yes
 102          [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '00001813-96D5-4444-AB33-63ADEDD368E2'.
 3146         ODBC--call failed.

Microsoft SQL Server 2012 (SP3-CU6-GDR) (KB3194724) - 11.0.6567.0 (X64)   Oct 10 2016 18:45:52   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

USE [AdpGL]
GO

/****** Object:  Table [dbo].[WorkTable]    Script Date: 01/19/2018 14:21:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[WorkTable](
	[ID] [nvarchar](max) NULL,
	[L Batch ID] [int] NULL,
	[Pay Group] [nvarchar](50) NULL,
	[Pay Group Description] [nvarchar](50) NULL,
	[General Ledger Account] [nvarchar](50) NULL,
	[General Ledger Cost Center] [nvarchar](50) NULL,
	[General Ledger Department] [nvarchar](50) NULL,
	[Work Center] [nvarchar](50) NULL,
	[Pay Period Ending Date] [nvarchar](50) NULL,
	[Hours] [nvarchar](50) NULL,
	[Amount] [nvarchar](50) NULL,
	[Week] [int] NULL,
	[Pay Type Code] [nvarchar](50) NULL,
	[Pay Type Description] [nvarchar](50) NULL,
	[File Number] [nvarchar](50) NULL,
	[Name] [nvarchar](50) NULL,
	[HOURLY SALARY] [nvarchar](50) NULL,
	[FULL TIME_PART TIME] [nvarchar](50) NULL,
	[ACTIVE_INACTIVE] [nvarchar](50) NULL,
	[HOURLY RATE] [nvarchar](50) NULL,
	[Load Date] [date] NULL,
	[Processed] [bit] NULL,
	[IDNew] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_WorkTable1] PRIMARY KEY CLUSTERED 
(
	[IDNew] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Open in new window

0
 
Dale FyeCommented:
When I said, change ReturnsRecords to Yes, I meant to then change the SQL of the query to :

SELECT * FROM dbo.WorkTable

I just want to see if this simple query returns an error.

But before you do that, can you copy the SQL from that querydef and post it here (as it exists now).
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Dale Fye also when i see the Select WorkTable pass through query i see just this

 ('00001813-96D5-4444-AB33-63ADEDD368E2', 10,'X2U', 'KANSAS CITY', '223200', '', '', '08402', '2016-12-31','', '-62.15', 51, 'T.LOC', 'EE LOCAL TAX', '710890', 'BLACKMON,GINGER M', 'S', 'RF', 'A', '40.28833506', '2017-01-01', 0), ('00008E64-C916-41EC-B174-93A5F4872F72', 10,'X2W', 'KANSAS CITY', '100351', '', '', '80103', '2016-12-17','', '-396.72', 51, 'D.  X', 'CHECKING 1', '085832', 'YOUNG,KIMBERLY K', 'H', 'RP', 'A', '16', '2017-01-01', 0)

Open in new window



this part of the query is missing:

INSERT INTO dbo.WorkTable (ID,[L Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], [General Ledger Cost Center], [General Ledger Department], [Work Center], [Pay Period Ending Date], Hours, Amount, Week, [Pay Type Code], [Pay Type Description], [File Number], Name, [HOURLY SALARY], [FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE],[Load Date],Processed) VALUES

Open in new window

0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
when i just run the select *  from dbo.WorkTable i am getting this error:

abcdefgh.PNG
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
when changed the return records to yes,  i clicked save the little abcdefg.PNG exclamation mark next to the select disappeared and it worked giving me the select records back from sql server.
0
 
Dale FyeCommented:
OK, so for some reason, we are not writing a complete SQL statement to the querydef.  I don't know how I missed this before but you never define the string strInsert.

At the top of your code create a line like the following, if it doesn't already exist.
dim strInsert as string

Open in new window


Then change the line that starts:
   qd.sql = "INSERT ...

Open in new window

to
strInsert = "INSERT ...

Open in new window


Then put a breakpoint in your code, after the line:  
qd.Execute line

Open in new window

You are not going to want to run this entire process, 2 records at a time, but you need to make sure that it is working so run through a couple of the executes.  

If it is working, then stop the code after several insert operations. and change the loop counter from 2 records at a time to 10, and see how that works.  If that gets through a couple of inserts, then stop the code again and change the loop counter from 10 to 25.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Dale Fye  yay!!!  Finally got it working all thanks to you bud you truly helped me fix the most frustrating part of the application.
0
 
Aravind RanganathanWindows Application DeveloperAuthor Commented:
Thanks Dale your the man :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.