VB Access query

davieboy_xr
11-12-2004, 10:34 AM
Is there any way i can create a query from vb and make a new query in access.
The reason why i want to to this is because i have distrubuted an application that uses access as its database and i now need to alter a database query or delete IT and create a new one.

Is there any way i can achieve this?

Dave

Dennis DVR
11-12-2004, 11:51 AM
Is there any way i can create a query from vb and make a new query in access.
The reason why i want to to this is because i have distrubuted an application that uses access as its database and i now need to alter a database query or delete IT and create a new one.

Is there any way i can achieve this?

Dave

What do you mean create a query from vb and make a new query in access? do you mean to create a query from vb and save it to access database as query?

Orca44
11-13-2004, 02:12 AM
I was faced with the same problem recently. It is pretty easy to create a query in Access using ADOX. Though I would still like to know if there is anyway to create one with a normal SQL statement.

Using the ADOX.Procedure class, it is easy to manipulate the queries.
Only problem is that the queries created in this way are not visible in Access.

MKoslof
11-13-2004, 11:07 AM
If using the Jet 4.0 Provider (Access 2000 and above) you can use ADO to create a new query:



Dim cn As ADODB.Connection
Dim sql As String

Set cn = New ADODB.connection

cn.ConnectionString = "YOUR ADO CONN"
cn.Open

'create a select query
sql = "CREATE View MyQuery AS SELECT Table1.* From Table1"
'execute it
cn.Execute sql

cn.close
set cn = nothing



Or use the ADOX as mentioned:



Dim cn As ADODB.Connection
Dim aComm As ADODB.Command
Dim aCat As ADOX.Catalog

Set cn = New ADODB.connection
Set aComm = New ADODB.command
Set aCat = New ADOX.Catalog

cn.ConnectionString = "YOUR ADO CONN"
cn.open

aComm.CommandText = "SELECT Table1.* From Table1"

With aCat
.ActiveConnection = cn
.Procedures.Append "MyNewQuery", ADOCommand
End With

Set aCat = Nothing
cn.Close
Set cn = Nothing
Set aComm = Nothing

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum