VBA SQL Query shuts down Excel

[dfarland]

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?

Dean

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

[RobDog888]

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

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

[dfarland]

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???

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

[RobDog888]

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.

[dfarland]

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

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

[dfarland]

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