Excel 97 ODBC connenction VBA 1

[BKer]

I have created a UserForm in Excel that allows the user to choose date ranges. The only other control on the form is a command button. I have been trying to figure out how to query an odbc DB with the input parameters and fill a spreadsheet. I can do it in Access pretty easy, but Excel is kind of slapping me around. Thanks.

bker

 

[kevinclark]

You'll need to set a reference to Microsoft ActiveX Data Objects 2.1 Library for this to run

Hope it will help


Private Sub CommandButton1_Click()
Dim objCon As ADODB.Connection
Dim rsSample As ADODB.Recordset
Dim strSQL As String
Dim intCol As Integer
Dim lngRow As Long
Dim strtDate As Date
Dim endDate As Date

strtDate = CDate(UserForm1.TextBox1.Text)
endDate = CDate(UserForm1.TextBox2.Text)

Set objCon = CreateObject("ADODB.Connection&quot
Set rsSample = New ADODB.Recordset
strSQL = "Select * From Sample_Table where Date_Created between #" & strtDate & "# and #" & endDate & "#"
objCon.Open "DSN_Name"
rsSample.Open strSQL, objCon, 3, 3

lngRow = 1

Do While Not rsSample.EOF
For intCol = 0 To rsSample.Fields.Count - 1
Sheets("Sheet1&quot.Cells(lngRow, intCol + 1).Value = rsSample.Fields(intCol).Value
Next intCol
lngRow = lngRow + 1
rsSample.MoveNext
Loop
End Sub

 

[BKer]

kevinclark,

Thanks for responding. I added the reference and then tried to execute it again and it gave me the following error. Data source name not found and no default driver specified. I can connect using msquery with the same dsn that I set up and retrieve records. Do you see anything wrong with this code. The dsn is complete except for the .dsn that it shows in the odbc device manager. I test the connection and it is fine.

Set objCon = CreateObject("ADODB.Connection&quot
Set rsSample = New ADODB.Recordset
strSQL = "Select * From v_AlarmEventHistory" objCon.Open "EH3456" 'This where the error occurred
rsSample.Open strSQL, objCon, 3, 3

Thanks again for the help,
bker

 

[Pcfred]

Try moving the DAO reference up to a higher priority.

 

[BKer]

I finally got this stuff to work right before the weekend. It was my connection string that was killing me, but after playing with the various typs of connection strings i found one that worked. Thanks for the help.

bker

Set objCon = CreateObject("ADODB.Connection&quot
Set rsSample = New ADODB.Recordset
strSQL = "Select * From Employees" ' where Date_Created between #" & strtDate & "# and #" & endDate & "#"
objCon.Open "driver={SQL SERVER};SERVER=EH3456;UID=sa;PWD=;DATABASE=Northwind;trusted_connection=No"
rsSample.Open strSQL, objCon

 

[kevinclark]

Just to confirm:

1)This is an Access Database ?
2)The DSN is configured under the System DSN tab in the ODBC Manager ?
3)The driver specified is Microsoft Access Driver (*.mdb) ?

 

[kevinclark]

Ah server - good going bker