Writing a Query

[k209310]

Is there a way to get VBA to write a query for an Access Database? If possible I would need to get this to run from an excel template.

 

[JonH]

You can write a query in VBA for using SQL. The syntax would be something like -

Dim dbs As Database
Dim rst As Recordset

Set Db = OpenDatabase("U:\My_Database.mdb")
Set rst = Db.OpenRecordset("SELECT * FROM tblPost")

This puts the query results into rst

You have to set a reference to Microsoft DAO 3.51 Object Library for this to work. I've not used Excel much in development but this compiles OKay and is certainly a method that works in Access and VB.

Jon.

 

[k209310]

Ok thanks for the advice.

I`m not a great VB programmer tho and am not to sure about referencing the Micrososoft Object Libary. Also the code you suggested fires back several error messages such as the opendatabase is not regognised.

To i get these error messages becasue i am not referencing the oject libary?

 

[Rich]

This site may help
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

 

[JonH]

Try the site Rich refers to for more details but basically you need to tell Excel that you wish to use DAO you do this by accessing the VB toolbar in Excel and opening the code editor, which is where you type the VBA. click Tools on the menu bar and from the list select References click alongside Microsoft DAO 3.51 object library. This allows Excel to recognise the code that I described earlier, without clicking this Excel just doesn't recognise the keywords - i.e Database, Recordset etc. Have a go and if you get stuck come back to us.

Good Luck,

Jon.

 

[k209310]

okay i have done that and the keywords are now regognised.

How is it possible to get the code to place the query on to a selected area of an excel spreadsheet?

 

[JonH]

K,
The following code pulls in a Recordset from a databse held in the U:\ drive based on the SQL at line 1.
Line 2 reserves the variable xlSht as an Object and line 3 defines that Object (xlSht) as the Current Sheet in Excel.
Line 4 moves the pointer to the beginning of the Recordset rst.
Line 5 begins a loop that will run until the Recordset is completely displayed.
Lines 6, 7 & 8 take the information from fields PostID, Post Name & DateRecieved.
Line 9 increments the variable X which just moves the next line of output one cell lower on Sheet1.
Line 10 moves the recordset pointer to the next record.
Finally Line 11 moves returns the code to Line 6.

Jon.

Dim dbs As Database
Dim rst As Recordset

Private Sub cmdRun_Click()

Set Db = OpenDatabase("U:\My_Database.mdb")
1 Set rst = Db.OpenRecordset("SELECT * FROM tblPost")

2 Dim xlSht As Object
3 Set xlSht = ActiveSheet

xlSht.Application.Visible = True

Dim X As Integer
X = 2

4 rst.MoveFirst

5 Do Until rst.EOF
6 xlSht.Range("A" & X) = rst!PostID
7 xlSht.Range("B" & X) = rst!PostName
8 xlSht.Range("C" & X) = rst!DateReceived
9 X = X + 1
10 rst.MoveNext
11 Loop

End Sub

 

[k209310]

Thats great its saved me alot of time and pain thanks. Ive persuaded my boss to send me on a course to learn VB so hopefully i can repay the favor one day.

I have changed the Query to contain a where statement. Whcih works fine

Set rst = Db.OpenRecordset("SELECT * FROM tblESTIMATE WHERE (`tblESTIMATE`.WEIGHT='20')")


However when i change the query to contain an input box (strFileName) and run the code to allow a user to enter criteria for the query to work...

strFileName = InputBox("Enter IWO Number")
strName = "FileName" & strFileName

Set Db = OpenDatabase("J:\DATA\ESTIMATI\Chedley\Estimating Files\access2000.mdb")
Set rst = Db.OpenRecordset("SELECT * FROM tblESTIMATE WHERE (`tblESTIMATE`.WEIGHT='strFileName')")

The code gives an error of `no current record` and highlights the rst.MoveFirst code. Is this the way that i should be doing this.

 

[neil_turner]

All you need to change is the way in which your code creates the query. What you need to do is seperate the text of the query from the variable you do this with "your query here " & your_Variable_here & " remainder of your query"

so hence the code you want to make this work is:

Set rst = Db.OpenRecordset("SELECT * FROM tblESTIMATE WHERE tblESTIMATE.WEIGHT= " & strFileName)

If weight is stored in the database as text then you will need to add some quotes so that the query knows its dealiing with text, hence : (note there is a ' in between the quote marks ")

Set rst = Db.OpenRecordset("SELECT * FROM tblESTIMATE WHERE tblESTIMATE.WEIGHT='" & strFileName& "'")

Cheers
Neil

 

[neil_turner]

Thinking about it a bit more, you may want to add the following lines into your code. The error you were getting was because the query was looking in the tblESTIMATE for an entry in the WEIGHT column that was = strFileName instead of what the value of the variable strFileName was.

To stop this happening when a user enters a value that is not within the column WEIGHT try adding this code after you set your recordset using the code supplied by JonH as an example:

1 Set rst = Db.OpenRecordset("SELECT * FROM tblPost")
if not(rst.eof and rst.eof) then

2 Dim xlSht As Object
3 Set xlSht = ActiveSheet

Dim X As Integer
X = 2

4 rst.MoveFirst

5 Do Until rst.EOF
6 xlSht.Range("A" & X) = rst!PostID
7 xlSht.Range("B" & X) = rst!PostName
8 xlSht.Range("C" & X) = rst!DateReceived
9 X = X + 1
10 rst.MoveNext
11 Loop

end if
rst.close

 

[neil_turner]

doh... that should be

if not (rst.eof and rst.bof) then

sorry about that wasn't thinking at the time of typing...

 

[k209310]

Thanks for the reply. I have implemented the code and adjusted it to fit my needs. Now i have a a run time error message claiming that there is

an error in the string in query expression 'tblESTIMATE=(the number entered in to the inputputbox)"

i have checked this i cant see an obviouse error. This is the code that is being used.

Set rst = Db.OpenRecordset("SELECT * FROM tblESTIMATE WHERE tblESTIMATE.WEIGHT=" & strFileName & "'")


if i use the other code that you provided i am given a data mismatch error (i preseume thi is why you offered the second line of code).

 

[neil_turner]

yeah thats right it appears your column WEIGHT is of type text. The problem with your code is that your missing a leading ' change the code so that it is exactly

Set rst = Db.OpenRecordset("SELECT * FROM tblESTIMATE WHERE tblESTIMATE.WEIGHT='" & strFileName & "'")

This should now work...

 

[k209310]

sorry that was my fault i miss read you original message and thanks for all your help it is very happily recieved and i appreciated you have you own work to do. I must surly owe you a night out.

Unfortuanatly the code you presented

If Not (rst.EOF And rst.BOF) Then

now gives the error message of an object variable or a with block variable is not set.

as i see it rst has been set so i think the object liabary concerning either of the code has not been set.

I am trying to learn from this

Thanks

 

[k209310]

Sorted thanks for all your help. I really do owe you all a drink.

Thanks

chris

 

[neil_turner]

No worries, I'm having problems with linking 2 tables together for an update statement and what Microsoft is telling me dosen't work, so quite happy to help instead bashing my head against the wall..

The problem your getting is that you have not closed the 'if' statment. An if statement has the following syntax

if your_condition_here then
resulting code
end if

For example if you wanted to check to see if a variable strName is equal to Bob then you would have:

if strName = "Bob" then


see the code example on above post. You need to add at the end your code just before the 'end sub'

end if
rst.close

 

[neil_turner]

dam, window dosen't support tab.... doh

what I was getting at is:

if strName = "bob" then
msgbox "hello Bob"
else
msgbox "your not bob"
end if

I hope this makes some sense now and that I've not completely confused you..

 

[k209310]

no you havent realy confused me. I know little bits about programming in C++ and Java but i have no experience at all in VB (A).

I`m a student doing a year out in industry so i`m learning (or at least trying) all the time and what you have just helped me with will hopefull impress my bosses in to giving me a huge huge bonus (unlikely, i know i have learnt that much already).

Thanks again and i hope i can repay the help someday.

 

[k209310]

Ok youve got me thinking now with the Bob thing.

If i wanted to put a an if statement to check the valididty if the weight entered in to the strFileName would this code be suitable

If strFileName = tblESTIMATE.Weight Then
MsgBox "The Data entered is Taken From IWO Number" _
& strFileName
Else
MsgBox "IWO Does Not Exist"
End If

i`m pretty sure that it would be bu i`m not to sure abour the first line to compare to the data in the database.

 

[JonH]

Glad to help Chris, good luck on your course. As a parting shot I'd use -

If rst.Recordcount > 0 then
Display the data in Excel
Else
Present an error/information message for the user
End If

This will catch the empty Recordset.

Jon.