VBA to SQL DB with user input and results to sheet

[Guest]

Hi folks,

I'm trying to create a little solution that will allow a user to open the
..xls and be prompted to input date values which would then be used to query
the remote SQL DB and return the results to sheet 1 of the .xls. Once
returned I'm going to have to perform some calculations in seperate subs()
that will fill a range based on an IF statement and calculate some more
figures based on the SUM of the IF range.

Right now I have a connection established to the DB but I'm having trouble
passing my parameters through the SQL query, and I haven't even starting to
think about how I'm going to return these results.

Any help is greatly appreciated as always... here is what I have so far...

' Define Input Date Parameters

Dim dtStartDate As Date
Dim dtEndDate As Date

' Ask for Data Range Input
dtStartDate = InputBox("Enter a starting date for the report range.",
"Beginning Date Range", "XX/XX/XXXX")
dtEndDate = InputBox("Enter End Date.", "Ending an ending date for the
report range.", "XX/XX/XXXX")

' Create the connection
Dim dbConn As Object
Dim Sql As String
Set dbConn = CreateObject("ADODB.Connection")
dbConn.Open "Driver={SQL Server}; Server=MY_SQL_SERVER;
Database=mydatabase;Uid=myuser; Pwd=mypassword;"
Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date >=
dtStartDate AND order_date <= dtEndDate"
dbConn.Execute (Sql)
dbConn.Close
Set dbConn = Nothing

I get an error on the dbConn.Excute "invalid column name 'dtStartDate'

Thanks in advance!!!

 

[Guest]

Hi folks,

I'm trying to create a little solution that will allow a user to open the
.xls and be prompted to input date values which would then be used to query
the remote SQL DB and return the results to sheet 1 of the .xls. Once
returned I'm going to have to perform some calculations in seperate subs()
that will fill a range based on an IF statement and calculate some more
figures based on the SUM of the IF range.

Right now I have a connection established to the DB but I'm having trouble
passing my parameters through the SQL query, and I haven't even starting to
think about how I'm going to return these results.

Any help is greatly appreciated as always... here is what I have so far...

' Define Input Date Parameters

Dim dtStartDate As Date
Dim dtEndDate As Date

' Ask for Data Range Input
dtStartDate = InputBox("Enter a starting date for the report range.",
"Beginning Date Range", "XX/XX/XXXX")
dtEndDate = InputBox("Enter End Date.", "Ending an ending date for the
report range.", "XX/XX/XXXX")

' Create the connection
Dim dbConn As Object
Dim Sql As String
Set dbConn = CreateObject("ADODB.Connection")
dbConn.Open "Driver={SQL Server}; Server=MY_SQL_SERVER;
Database=mydatabase;Uid=myuser; Pwd=mypassword;"
Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date >=
dtStartDate AND order_date <= dtEndDate"
dbConn.Execute (Sql)
dbConn.Close
Set dbConn = Nothing

I get an error on the dbConn.Excute "invalid column name 'dtStartDate'

Thanks in advance!!!

Hurdle No. 1 completed. I figured out my syntax error in my SQL statement.
It was actualy two problems in one. First I needed to concat my statement
string with my variables "where oe_hdr.order_date >= " & dtStartDate & " ...
Then I ran into the problem of datatype mismatchs which I was albe to fix by
changes my DateTime variables into strings.

Now I have to figure out how to create the recordset and pass it back to my
sheet 1.

Any help is always appreciated.

Thanks!!!

 

[MH]

Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date >=

dtStartDate AND order_date <= dtEndDate"

The above would be more efficient using BETWEEN instead of > lowest and <
highest, you have to include the hashes to stop the comparrison from taking
your dates as strings:

Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date
Between #" &
dtStartDate & "# AND #" & dtEndDate & "#"

Point 2.

You need to create a recordset object to store the returned records and then
look at the CopyFromRecordset method in Excel VBA help.

Post back if you get stuck again.

MH

 

[Guest]

MH,
Thanks for jumping in... I got what your saying about the BETWEEN being more
effiecient, thanks.

I create my recordset object
Set rst = New ADODB.Recordset
and I can see how to get it to return to the sheet via the VBA help, but how
do I tell it to equal the results of the Sql string?

 

[MH]

'Dim the recordset object and initiate a new instance of it
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
....
'Open the recordset here
rs.Open strSQL, dbConn
....
'Close the recordset object and release the memory space by setting the
object to nothing
rs.Close
Set rs = Nothing


I would use strSQL for your variable name instead of simply sql as this may
cause confusion later on.

HTH

MH

 

[Guest]

ok, I've got the recordset and I know my date range contains data, I evne
have the code popupating the forst row with the field names, but for the life
of me I can't figure out why I'm not returning any data??

 

[Tim Williams]

Replace your # around the dates with '
As I recall ADO doesn't like #.

If that doesn't work then post your current code and the generated SQL statement.

 

[Guest]

Fantastic, that did it!

Replace your # around the dates with '
As I recall ADO doesn't like #.

If that doesn't work then post your current code and the generated SQL statement.