wilko100
asked on
VB6 running access query
Hi
Im new to VB not sure how to run an access query through VB6
I have a button on a form that i want to update a table in access by using a query i created in access
how easy is it to get VB to run the query at the click if the button?
maybe something like:
Private Sub btnUpdate_Click()
Dim rs As Recordset
'Dim qd As QueryDef
Dim mydb As Database
Set mydb = db.OpenRecordset("querynam e")
'Set rs = db.OpenRecordset
'If rs.RecordCount > 0 Then
'Do While Not rs.EOF
'rs.MoveNext
'Loop
'End If
'rs.Close
'Set rs = Nothing
'Exit Sub
End Sub
Im new to VB not sure how to run an access query through VB6
I have a button on a form that i want to update a table in access by using a query i created in access
how easy is it to get VB to run the query at the click if the button?
maybe something like:
Private Sub btnUpdate_Click()
Dim rs As Recordset
'Dim qd As QueryDef
Dim mydb As Database
Set mydb = db.OpenRecordset("querynam
'Set rs = db.OpenRecordset
'If rs.RecordCount > 0 Then
'Do While Not rs.EOF
'rs.MoveNext
'Loop
'End If
'rs.Close
'Set rs = Nothing
'Exit Sub
End Sub
private sub btnUpdate_Click_click
Dim rs As Recordset
dim mydb As Database
Set mydb = db.OpenRecordset("c:\your database name")
Set rs = db.OpenRecordset("select * from yourtablename where field ='" & text1.text & "'"
If rs.RecordCount > 0 Then
rs.fields("yourfield")=tex t1.text
rs.updae
rs.Close
endif
Set rs = Nothing
End Sub
Dim rs As Recordset
dim mydb As Database
Set mydb = db.OpenRecordset("c:\your database name")
Set rs = db.OpenRecordset("select * from yourtablename where field ='" & text1.text & "'"
If rs.RecordCount > 0 Then
rs.fields("yourfield")=tex
rs.updae
rs.Close
endif
Set rs = Nothing
End Sub
You can substitute the "Select.... " codes of the previous examples and simply enter the Query name created in Access. This helps if your Access query includes joins and such. Instead of entering all this as a long SQL string in VB, you simply open the query as a recordset (just like a table).
You will still need a WHERE (perhaps) and such..... but it's easier.
Scott C
You will still need a WHERE (perhaps) and such..... but it's easier.
Scott C
ASKER
thanks for the reply yeah there makes sense
however when i enter in my database name then run it, it throughs an error:
Runtime error 3078
Microsoft jet engine cannot find the input table or query
C:\DatabaseName.mdb' make sure it exists
but its there!!
this is the code:
Private Sub btnUpdate_Click()
Dim rs As Recordset
Dim mydb As Database
Set mydb = db.OpenRecordset("C:\Datab aseName") *fails here*
Set rs = db.OpenRecordset("QueryNam e")
If rs.RecordCount > 0 Then
'rs.Fields("yourfield") = Text1.Text
rs.Update
rs.Close
End If
Set rs = Nothing
End Sub Its doing my nut in!!
however when i enter in my database name then run it, it throughs an error:
Runtime error 3078
Microsoft jet engine cannot find the input table or query
C:\DatabaseName.mdb' make sure it exists
but its there!!
this is the code:
Private Sub btnUpdate_Click()
Dim rs As Recordset
Dim mydb As Database
Set mydb = db.OpenRecordset("C:\Datab
Set rs = db.OpenRecordset("QueryNam
If rs.RecordCount > 0 Then
'rs.Fields("yourfield") = Text1.Text
rs.Update
rs.Close
End If
Set rs = Nothing
End Sub Its doing my nut in!!
Change this line:
Set mydb = db.OpenRecordset("C:\Datab aseName") *fails here*
To:
Set mydb = DBEngine.OpenDatabase("C:\ DatabaseNa me")
Set mydb = db.OpenRecordset("C:\Datab
To:
Set mydb = DBEngine.OpenDatabase("C:\
ASKER
entered that and now gets past that but now it failing on the line below!
Set rs = db.OpenRecordset("QueryNam e")
says error runtime error 3919
invalid operation
Why why! ? lol
Set rs = db.OpenRecordset("QueryNam
says error runtime error 3919
invalid operation
Why why! ? lol
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
*SORTED* (excuse the indentation hehe)
Private Sub btnUpdate_Click()
Dim rA As Integer
Dim db As New ADODB.Connection
db.ConnectionString = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta Source = C:\dataBaseName.mdb"
db.Open
db.Execute "INSERT INTO TableA SELECT * FROM TableB", rA
If rA > 0 Then
MsgBox "Completed!!"
End If
db.Close
Set db = Nothing
End Sub
Thanks for the help!! much appriciated
Private Sub btnUpdate_Click()
Dim rA As Integer
Dim db As New ADODB.Connection
db.ConnectionString = "Provider=Microsoft.Jet.OL
db.Open
db.Execute "INSERT INTO TableA SELECT * FROM TableB", rA
If rA > 0 Then
MsgBox "Completed!!"
End If
db.Close
Set db = Nothing
End Sub
Thanks for the help!! much appriciated
Dim rs As Recordset
dim mydb As Database
private sub command1_click
Set mydb = db.OpenRecordset("c:\your database name")
Set rs = db.OpenRecordset("select * from yourtablename where field ='" & text1.text & "'"
If rs.RecordCount > 0 Then
rs.fields("yourfield")=tex
rs.updae
rs.Close
endif
Set rs = Nothing
end sub
'Exit Sub
End Sub