VB6 Equivalence to docmd.openQuery??

Hello,

I'll let this be the dumb questions of the year (it's pass 2:00 am)..

All I'm trying to do is run an append query that I have in MS Access from VB6. I thought this would be just as simple as VBA (docmd.openquery), but obviously, I'm dead wrong.

I already have the connection and everything, but I guess I'm trying to find the easiest way of simply running a query from MS Access using VB6 (ADO).

I would appreciate any feedback ( I was thinking adodb.command??). Thanks.

This is what I have..

Public Sub ConnectMonthlyPhones()
Public cnnMonthlyPhones As New ADODB.Connection

If cnnMonthlyPhones.State = 1 Then Exit Sub 'MS Access DB already connected

cnnMonthlyPhones.ConnectionString = "Provider=Microsoft.jet.OLEDB.4.0;" _
& "Data Source=" & "C:\Database\RS_PhoneList.mdb" & ";Persist Security Info=False"

cnnMonthlyPhones.Open

End Sub

Public recPhones As New ADODB.Recordset
Public strSQL As String

strSQL = "SELECT Phones FROM [MainList_Phones]" 'Open table

With recVDNs
.Open strSQL, cnnMonthlyPhones, adOpenKeyset, adLockOptimistic
DoEvents

.MoveFirst
Do Until recPhones.EOF = True 'Get phones from Main Phones table
If Len(recPhones!Phone) > 10 Then
Beep
recPhones.Close
MsgBox ("Record selected is not consistent with a Phone format."), vbOKOnly
Exit Sub
Else
strPhone = recPhones!Phone
lblStatus.Caption = "Getting report for Phone " & recPhones!Phone

If AppendData("Q_VDN_Avaya_AppendData") = False Then
MsgBox ("query did not run")
Beep
End If
End If

recPhones.MoveNext
Loop
End With

Public Function AppendData(strQueryName As String) As Boolean
dim recRunQuery as new ADODB.command???

If cnnMonthlyPhones.State = 0 Then 'If no connection, connect to db
Call ConnectMonthlyPhones 'Open db connection.
End If
''THIS IS WHERE I"M HAVING PROBLEMS!!

''''''here......I just want to open an append query that's already created in the database

Please use the .. tags when you post your code. Edit or reply to this post to see how.

Thank you.

Look up the 'Execute' method of the Connection object in your VB help.

Look up the 'Execute' method of the Connection object in your VB help.

I've tried different methods. This is my latest..Nothing has worked for me so far... Thanks.


'this one
rec query. execute strsQueryName

'and this one
recquery.open strQueryName,,,adCmdTableDirect

I don't know about ADO, but here's how to do it using DAO:

'
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim c As DAO.DBEngine
Dim DbName As String
Dim QrName As String

DbName = "PATH TO DATABASE"
QrName = "NAME OF QUERY"

Set c = New DAO.DBEngine
Set db = c.OpenDatabase(DbName)
Set qdf = db.QueryDefs(QrName)

qdf.Execute

Set qdf = Nothing
db.Close
Set db = Nothing
Set c = Nothing

I've tried different methods. This is my latest..Nothing has worked for me so far... Thanks.


'this one
rec query. execute strsQueryName

'and this one
recquery.open strQueryName,,,adCmdTableDirect

He meant you use the Connection Object to execute a query as in:

Connection.execute <strqueryname>

That'll work. :D

He meant you use the Connection Object to execute a query as in:

Connection.execute <strqueryname>

That'll work. :D

Thanks everyone. I couldn't do it in ADO, so I went back to my old roots and called the MS Access object. Then used VBA......I cheated:


Public Function AppendData(strQueryName As String) As Boolean
Dim Access As New Access.Application

If cnnMonthlyPhones.State = 1 Then 'If DB open, then close
cnnMonthlyPhones.Close
End If

Access.OpenCurrentDatabase ("C:\MonthlyReports\RS_Phones.mdb")

DoCmd.SetWarnings False 'don't show warning prompts
Access.DoCmd.OpenQuery strQueryName 'Run query
DoCmd.SetWarnings True 'Set warning prompts back on

AppendData = True


End Function

Using Access is not a good idea. You've been told 2 different ways to do it that work.

That'll work but it is filled with landmines... :)

Why didn't the ado connection object work? It's ridiculously easy to execute a query.

That'll work but it is filled with landmines... :)

Why didn't the ado connection object work? It's ridiculously easy to execute a query.

Yeah. :o
That was my first thought, but it's not working...

I made the connection..

cnnMonthlyPhone.Open (coded aboved)

This is the last code I tried on my last attempt..



cnnMonthlyPhone.execute (strQueryName)

An example I saw online had "conn.Execute 'EXECUTE MyQuery'" for a query that wasn't a select query.

Anywya, I tested the DAO way and it works.

Yeah. :o
That was my first thought, but it's not working...

I made the connection..

cnnMonthlyPhone.Open (coded aboved)

This is the last code I tried on my last attempt..



cnnMonthlyPhone.execute (strQueryName)



Try it without the ()


cnnMonthlyPhone.execute strQueryName

You should only use parenthesis when you want a return value. (I think..lol) But anyways it works fine for me without the ().

Saying "it doesn't work" is about the least informative description possible. What happened? Did an error occur? If so, what?

As much detail as possible please.