VBA SQL Query shuts down Excel

Have the following code. When I run this the first query works and the second one shuts down Excel. When I step into this, it does it's dirty deed on the SQLExecQuery line.

Any ideas out there? :confused:

Dean

VB Code:

Sub UsingSQLOpen()
 
Dim Chan As Variant
Dim DataDate As String
DataDate = Range("sheet2!B3")
    Chan = SQLOpen("DSN=SMP_DB;UID=sa;PWD=suprsonic")
    SQLExecQuery Chan, "SELECT * FROM InventoryTotalsHistory Where " & _
                    "ith_dtBusinessDate Between " & DataDate & ""
    SQLRetrieve Chan, Range("sheet2!D2")
    SQLClose Chan
    
    Chan = SQLOpen("DSN=SMP_DB;UID=sa;PWD=suprsonic")
    SQLExecQuery Chan, "SELECT * FROM InventoryFoodCostHistory Where " & _
                    "ifc_dtBusinessDate Between " & DataDate & ""
    SQLRetrieve Chan, Range("sheet2!AD2")
    SQLClose Chan
End Sub

First you need to add some error handling so you can determine what the error message is.

VB Code:

Sub UsingSQLOpen()
    On Error GoTo MyError
 
    Dim Chan As Variant 'Shouldnt be declared as a variant but a ADODB.Connection object (I think your using ADO?)
    Dim DataDate As String
    DataDate = Range("sheet2!B3")
    Chan = SQLOpen("DSN=SMP_DB;UID=sa;PWD=suprsonic")
    SQLExecQuery Chan, "SELECT * FROM InventoryTotalsHistory Where " & _
                    "ith_dtBusinessDate Between " & DataDate & ""
    SQLRetrieve Chan, Range("sheet2!D2")
    SQLClose Chan
    
    Chan = SQLOpen("DSN=SMP_DB;UID=sa;PWD=suprsonic")
    SQLExecQuery Chan, "SELECT * FROM InventoryFoodCostHistory Where " & _
                    "ifc_dtBusinessDate Between " & DataDate & ""
    SQLRetrieve Chan, Range("sheet2!AD2")
    SQLClose Chan
    Exit Sub
MyError:
    MsgBox Err.Number & " - " & Err.description
End Sub

Have changed the code to this. Get an error stating the dim chan as adodb.connection is not defined. I have the odbc addin installed???

VB Code:

Sub UsingSQLOpen()
    On Error GoTo MyError
 
    Dim Chan As ADODB.Connection
    Dim DataDate As String
    DataDate = Range("sheet2!B3")
    Chan = SQLOpen("DSN=SMP_DB;UID=sa;PWD=suprsonic")
    SQLExecQuery Chan, "SELECT * FROM InventoryTotalsHistory Where " & _
                    "ith_dtBusinessDate Between " & DataDate & ""
    SQLRetrieve Chan, Range("sheet2!D2")
    SQLClose Chan
    
    Chan = SQLOpen("DSN=SMP_DB;UID=sa;PWD=suprsonic")
    SQLExecQuery Chan, "SELECT * FROM InventoryFoodCostHistory Where " & _
                    "ifc_dtBusinessDate Between " & DataDate & ""
    SQLRetrieve Chan, Range("sheet2!AD2")
    SQLClose Chan
    Exit Sub
MyError:
    MsgBox Err.Number & " - " & Err.Description
End Sub

Please use the [Highlight=VB] tags as it makes it easier to read code. ;)

What was the error message? I havent seen any Add-In for ADO so you should make sure you have a reference set to "Microsoft ActiveX Data Objects 2.x Library" as that is probably the error since you say it stops at that line.

Ok on the vb tags, sorry I'm a newbie... :wave:

The addin is the odbc addin. As far as I know, it has to be installed to use the adodb functions.

Thanks for your help.

Dean

Here is the latest code. Now I get an error "91 - Object variable or With block variable not set" when I run this line of code [Chan = SQLOpen("DSN=SMP_DB;UID=sa;PWD=suprsonic")]. Not sure what I'm missing?

Sub UsingSQLOpen()
On Error GoTo MyError

Dim Chan As ADODB.Connection
Dim DataDate As String
DataDate = Range("sheet2!B3")
Chan = SQLOpen("DSN=SMP_DB;UID=sa;PWD=suprsonic")
SQLExecQuery Chan, "SELECT * FROM InventoryTotalsHistory Where " & _
"ith_dtBusinessDate Between " & DataDate & ""
SQLRetrieve Chan, Range("sheet2!D2")
SQLClose Chan

Chan = SQLOpen("DSN=SMP_DB;UID=sa;PWD=suprsonic")
SQLExecQuery Chan, "SELECT * FROM InventoryFoodCostHistory Where " & _
"ifc_dtBusinessDate Between " & DataDate & ""
SQLRetrieve Chan, Range("sheet2!AD2")
SQLClose Chan
Exit Sub
MyError:
MsgBox Err.Number & " - " & Err.Description
End Sub