Using SQL to extract data from database file

[Goober]

Dear all,



I need to create a userform in Excel VBA to extract data from a MS
Access database file using SQL.



I've enabled 'Microsoft DAO 3.6 Object Library in the Excel VBA
reference list for this job.



An extract from my script is:

-------------------------------------



Dim WrkDefault As Workspace

Dim FSS2000 As Database

Dim RS1 As Recordset

Dim DatabaseName As String

Dim SQLString As String





On Error Resume Next

DatabaseName = ThisWorkbook.Path & "\ORDER2000"







SQLSTRING = "SELECT Order_No, Month, Product_A_Quantity FROM Orders"

SQLSTRING = SQLSTRING & "WHERE Product_A_Quantity > TxtQuantity"

SQLSTRING = SQLSTRING & "ORDER BY Product_A_Quantity"





Set WrkDefault = DBEngine.Workspaces(0)



Set ORDER2000 = WrkDefault.OpenDatabase(Name:=DatabaseName,
ReadOnly:=False)

Set RS1 = ORDER2000.OpenRecordset(Name:=SQLString,
Type:=dbOpenDynaset)



Worksheets("Sheet1").Range("a1").CopyFromRecordset RS1



ORDER2000.Close







End Sub



------------------------------------------------



The Order_No, Month, Product_A_Quantity are fields from the Orders table
in my database file, called ORDER2000.mdb



TxtQuantity is supposed to be an input entered by the user in the
userform. I don't think I'm writing the script the correct way because
it doesn't seem to run. Is there some signs or brackets that I need to
put in order for TxtQuantity to be identified as a user-input variable?



Thank you.

 

[mikezcg]

SQLSTRING = "SELECT Order_No, Month, Product_A_Quantity FROM Orders "

SQLSTRING = SQLSTRING & "WHERE Product_A_Quantity > " &
TxtQuantity.value

SQLSTRING = SQLSTRING & " ORDER BY Product_A_Quantity"





add spaces to the end of all strings or at the begining as in the
order by clause



if your geting a # from the txtQnt you dont need to add ' ' but if its
a string :



Sql= Sql & "'" & txtQty.value & "'"

"'" = " ' "



if its a date:



Sql= Sql & "#" & txtQty.value & "#"