VBA to SQL DB with user input and results to sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!!!
 
Stephen said:
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!!!
Click to expand...

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!!!
 
Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date >=
dtStartDate AND order_date <= dtEndDate"
Click to expand...

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
 
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?
 
'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
 
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??
 
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.
 
Fantastic, that did it!

Tim Williams said:
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.
Click to expand...
 
Back
Top