Member Login

Username
Password
Forget Password
New Sign Up
Search Forum

Buy Support
Incidents

If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:

Click Here
If you can't login and post questions or you are having trouble viewing forum posts:
Click Here
Callback
Support

If you live in USA, UK, Canada, Australia or New Zealand, you can leave us details on your question and request us to call you back and discuss them with you personally  (charges apply).

Click Here
 
Buy Support
Incidents
If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:
Click Here

Forum : Problem with Build Assembly using VBASearch Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC v9 Forum

 New Topic 
 
 Post Reply 
[1]  
 Problem with Build Assembly using VBA 
 Author   Message 
  Gerd 
  
 Group: Members 
 Posts: 28 
 Joined: 2008-12-09 
 Profile
 Posted : 2008-12-28 22:54:30

QB 2008 Prem, QODBC V9

For some of my sales i need to build assembly items. I wrote a VBA script that extracts the assembly items from a sales receipt or invoice and puts it on an Excel. Next I process the Excel and do an INSERT INTO BuildAssembly. When building the assembly I want to use the sales receipt date as the transaction date for the assembly build.

Everything works fine if I don't use the transaction date or hard code it in the SQL string (see code). The problem is when i use the transaction date as a parameter in the SQL string and execute it. I get an Run-time error '-2147217887 (80040e21)' Automation error.

In debug mode I see that my TxnDate (defined as String) has the correct format of {d'2008-12-23'}, nevertheless, I gt the run-time error.

I cannot figure out what's wrong with my SQL string and would appreciate if somebody could have a look. I have put several SQL strings as comment to show how the parameters are passed. The one with the TxnDate is giving me the grieve.

Here is the code

Sub ADOExcelQuickBooksBuildAssembly()
   
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adUseClient = 3

    Dim oConnection
    Dim oRecordset
   
    Dim sSQLStr1 As String
    Dim lRow As Long
    Dim sItemName As String
    Dim dBuildQty As Double
    Dim sRefNumber As String
    Dim sTxnDate As String
    Dim sDate As String
    Dim iRowCount As Integer
    Dim sMemo As String
   
    Set oConnection = CreateObject("ADODB.Connection")
    Set oRecordset = CreateObject("ADODB.Recordset")
    
    ' Connect to server and get table
    oConnection.Open "DSN=Quickbooks Data;OLE DB Services=-2"
    
    'Update ItemInventory for each row
        For lRow = 2 To ActiveSheet.UsedRange.Rows.Count
       
        'Set build assembly values
        sDate = Format(ActiveSheet.Cells(lRow, 1), "yyyy-mm-dd")
        sTxnDate = "{d'" & sDate & "'}"
        sRefNumber = ActiveSheet.Cells(lRow, 2)
        sItemName = ActiveSheet.Cells(lRow, 3)
        dBuildQty = ActiveSheet.Cells(lRow, 5)
        sMemo = "QB Sales #: " & ActiveSheet.Cells(lRow, 2)
        s = "INSERT INTO BuildAssembly (TxnDate) VALUES ({d'" & sDate & "'})"
        sSQLStr1 = "INSERT INTO BuildAssembly (ItemInventoryAssemblyRefFullName, TxnDate, RefNumber, Memo, QuantityToBuild) VALUES ('" & sItemName & "', '" & sTxnDate & "', '" & sRefNumber & "', '" & sMemo & "', " & dBuildQty & ")"
        'sSQLStr1 = "INSERT INTO BuildAssembly (ItemInventoryAssemblyRefFullName, RefNumber, QuantityToBuild, Memo) VALUES ('" & sItemName & "', '" & sRefNumber & "', " & dBuildQty & ", '" & sMemo & "')"
        'sSQLStr1 = "INSERT INTO BuildAssembly (ItemInventoryAssemblyRefFullName, TxnDate, RefNumber, QuantityToBuild, Memo) VALUES ('" & sItemName & "', {d'2008-12-20'}, '" & sRefNumber & "', " & dBuildQty & ", '" & sMemo & "')"
       
        'Now update record
        oRecordset.Open sSQLStr1, oConnection, adOpenStatic, adLockOptimistic
       
    Next

    ' Stop application updating
    Application.ScreenUpdating = False
    
    ' Tidy up
    oConnection.Close
    Set oConnection = Nothing
End Sub

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2008-12-29 09:21:24

Try changing sDate to be a date variable instead of a striing.

 

  Top 
  Gerd 
  
 Group: Members 
 Posts: 28 
 Joined: 2008-12-09 
 Profile
 Posted : 2008-12-29 09:35:51
If I change it to a date field the format does not return the yyyy-mm-dd format but system format (mm/dd/yyyy) 

  Top 
  Gerd 
  
 Group: Members 
 Posts: 28 
 Joined: 2008-12-09 
 Profile
 Posted : 2008-12-30 03:25:27

I did some more searches on your forum and on Google and found several references where people used a transaction date in an SQL string. Unfortunately none specified how they declarded the transaction date field and how they populated its value.

It looks like that the SQL processor has a problem with the string field even if it has the same format as a hard-coded date field.

One poster had a snippet with """ & TxnDate & """ (with 3 double quotes) which I tried as well, but still, i get the error.

I really would appreciate any help with this.

 

  Top 
  Gerd 
  
 Group: Members 
 Posts: 28 
 Joined: 2008-12-09 
 Profile
 Posted : 2009-01-02 04:08:08

I was wondering if somebody is using some VBA scripts that include a date. I would be interested in finding out how the date field is declared and passed to the SQL string.

I need to find a solution for this and can't believe that I can't pass the transaction date. I must be doing something wrong.

 

  Top 
  Gerd 
  
 Group: Members 
 Posts: 28 
 Joined: 2008-12-09 
 Profile
 Posted : 2009-01-05 05:47:20

After doing quite some additional googling, checking and testing I found out that the problem is only with an INSERT. I added the same date field to a SELECT, which worked without a problem and showed the requested record with the correct date.

However, when trying to do an insert and passing the date parameter in the VALUES section the VBA triggers the error and does not do the INSERT.

So, after the same SQL processor is used for the SELECT and the INSERT I would have assumed that the date parameter is either accepted or rejected for any SQL command.

Tom, would it be possible to check with your developers why that is and if there is anything I can do to finally get my INSERT to work with the date parameter? I am really getting desperate to get this fixed as I do assembly builds on a daily basis.

Thanks in advance.

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2009-01-05 11:24:52

QODBC has no problems with dates within inserts. This issue has to do with Excel not accepting the syntax. For MS Access applications dates are used with a #.

See: How are dates formatted in SQL queries when using the QuickBooks generated time stamps?  for more.

 

  Top 
  Gerd 
  
 Group: Members 
 Posts: 28 
 Joined: 2008-12-09 
 Profile
 Posted : 2009-01-06 05:55:03

Thanks for your reply, Tom.

I don't want to be a pest but there is obviously a difference when doing a select or an insert. When using the correctly formatted date string on the select i don't get any error messages. But using the very same date string in the insert triggers the error. That does tell me that the insert cannot handle the date string.

I used the select and insert in the very same VBA to ensure there is no difference with the date variable.

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2009-01-06 12:23:56
Please run the insert statement using VB Demo to work out if it's a QODBC problem or a MS Excel problem. 

  Top 
  Gerd 
  
 Group: Members 
 Posts: 28 
 Joined: 2008-12-09 
 Profile
 Posted : 2009-01-07 06:12:58

Tom, VB Demo works as i have to type the date in the proper format.

The point i wanted to make is that using a date parameter, properly formatted as {d'YYYY-MM-DD'}, should work on a select AND on an insert or not work at all. this is within the same VBA.

When using the above hardcoded transaction date in my insert SQL string to build the assembly it works without a problem. However, when passing the very same value as a parameter (type string) i get an abend. displaying the transaction date in debug mode shows that it is propperly formatted, just like typing the value.

It also would be helpful if we could "access" the error routine you already use in the VB Demo program after executing an SQL command. such an error routine would be very helpful in determining what exactly the problem is.

 

  Top 
  Gerd 
  
 Group: Members 
 Posts: 28 
 Joined: 2008-12-09 
 Profile
 Posted : 2009-01-07 07:00:48

Tom, I think I found the problem with the transaction date.

The excel cell holding the date from the previous sales receipt extract was formatted (by default) as M/D/YYYY. I just changed this format to MM/DD/YYYY and sure enough the INSERT worked.

I have no idea why this would make a difference especially after the date parameter in debug mode showed the leading zeros for month and day, thus, i assumed the date was the very same as when hardcoding it.

It still would be nice if you could provide an error routine we could call after SQL commands get executed. i checked the VB Demo source and you call a routine right after executing the SQL command, but that error routine is somewhere "hidden" in a .dll file.

Is there such an error routine we could access or could you provide one? it would be very helpful having access the same error or informational messages that you display in VB Demo.

 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to