Testing connection string and SQL statment

[joehoe007]

Hello All, New to VBA but not to excel. I'm trying to get this code to pull in data that I usually have too manually copy over. Trying to validate if the connection string / SQL statement or record set is not working. I don?t get in error. It runs and then ends. does not populate my spread sheet, no error messages either.

Any help would be great...


Private Sub CommandButton1_Click()
Const strConStr = "Provider=MSDASQL;DSN=xxx;SRVR=xx;DB=xxxx;UID=xxx;PWD=xxx"
Dim STRSQL_BUFFER As String
Dim adoConnection As New ADODB.Connection
Dim adoRecordset As New ADODB.Recordset
Dim dtmStart As Date
Dim dtmEnd As Date
Dim strcell As Integer
dtmStart = #8/20/2008#
dtmEnd = #8/21/2008#
If adoConnection.State <> 1 Then
adoConnection.Open strConStr
End If

STRSQL_BUFFER = "SELECT * FROM d_camp_define WHERE convert(varchar(10), Dateadd(ss, create_tstamp,'01/01/1970'),101) BETWEEN '" & dtmStart & "' AND '" & dtmEnd & "'"

If adoConnection.State = 1 Then
adoRecordset.Open STRSQL_BUFFER, adoConnection
End If

Do While Not adoRecordset.EOF
Me.range("a" & CStr(strcell)) = adoRecordset.Fields(1)
strcell = strcell + 1
adoRecordset.MoveNext
Loop


adoConnection.Close
Set adoRecordset = Nothing
Set adoConnection = Nothing
End Sub


Thanks

[herzberg]

STRSQL_BUFFER = "SELECT * FROM d_camp_define WHERE convert(varchar(10), Dateadd(ss, create_tstamp,'01/01/1970'),101) BETWEEN '" & dtmStart & "' AND '" & dtmEnd & "'"

I'm not too good with SQL statements but I suppose a field needs to be specified before a WHERE statement can be used? As in "SELECT [d_camp_define].Date FROM [d_camp_define] WHERE ([d_camp_define].Date = "01/01/1900);" or something to that effect.

[tstav]

1. What kind of DB are you accessing? Access or SQL Server? Depending on that, the connectionString should be built accordingly.
Creating a .udl file helps a lot in building this string.

A usual SQL Server connectionString looks like:
connectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;" & _
"User ID=myname; Password=mypassword;" & _
"Data Source=servername;Initial Catalog=databasename"

And a usual Access connectionString looks like:
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft " & _
"Office\OFFICE11\SAMPLES\Northwind.mdb;Persist Security Info=False"

2. I don't need to remember numeric values for connection.state property if I use enumerations:
I would use
[vba]'If adoConnection.State <> 1
If adoConnection.State <> ObjectStateEnum.adStateOpen Then
adoConnection.Open strConStr
End If[/vba]

3. As for the RecordSet.Open piece of code, I would make it like so (I use the connectionString and not the connection object, since connection has already been opened explicitly):
[vba]adoRecordset.Open STRSQL_BUFFER, strConStr, adOpenForwardOnly, , adCmdText[/vba]

4. In the 'Do While Not adoRecordset.EOF' loop, shouldn't the 'strcell = strcell + 1' be right below the 'Do while'? Otherwise in the first iteration you'd be trying to put a value to cell("A0").
By the way, better dimension the strcell as long (row numbers could get past integer max).

5. Doesn't 'BETWEEN' work only for Dates and Numbers? I can see you're redefining a date as string varchar(10) and then you are using BETWEEN... (?)

6. What is create_tstamp?

7. Remember when typing your answer posts, to first select your code and then press the green VBA button. This way your code will be indented and easier to read.

Besides all the above, it really puzzles me that you say you get no error message.... Have you stepped through the code (with F8)?

<Edit>: I added bullets number 6 and 7.

[rory]

The fact that you are not getting an error makes me think that your recordset has no data - therefore .EOF is True and you never hit tstav's point 4 problem.
In addition to those other points, it's very inefficient to use SELECT * and then only use one field. If you just retrieve the data you want in the recordset, you can then use the Range.CopyFromRecordset method rather than populating cells individually in a loop.