Member Avatar for zilonox

I have an application that uses an *.accdb file as the back-end database. Connecting to and retrieving data from the access file is not an issue and is working quite well.

However, as a precaution, I have the application first check to make sure the database file exists in the application's folder while it loads up. As a convenience, if the database file is not found, then the application creates a new (structured, yet empty) database. The creation of the actual *.accdb file and the creation of the tables is complete and is also working well.

What I cannot figure out, however, is how to create the queries in the *.accdb file.

I use ADOX to create the *.accdb file, and System.Data.OleDb objects to create the tables (using "CREATE TABLE blah (field1 type1, field2 type2, PRIMARY KEY (field1)" sql statements).

Now, I know that if I were programming just in Access VBA, I could add queries using the QueryDef collection, but I can't seem to get it to work from VB2005 (no autosense and program won't build because of the QueryDef DIM statement). Is it perhaps something as simple as not having the proper reference?

I'd appreciate any help on the matter.

Thank you.
Z

Edited by zilonox because: n/a
  • 1 Contributor
  • 1 Reply
  • 293 Views
  • 22 Hours Discussion Span
  • Latest Post Latest Post by zilonox
Member Avatar for zilonox

Okay, I have to say that this is the second question I've asked and the second question I've had to solve for myself. I really don't see a reason to continue my membership here.

For those that are looking for the answer, I finally found it on another site (http://forums.devx.com/showthread.php?t=48215). I'll post the code below though just in case the aforementioned link ever expires.

MANY THANKS TO PAUL CLEMENT ON THE devx.com FORUMS!!!

Before using this code, you'll have to add a reference to both ADODB and Microsoft ADO Ext 2.x for DDL and Security.

Function CreateQueryDefADOX()

Dim cat As New ADOX.Catalog
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FilePathandName & ";Persist Security Info=False;"

cat.ActiveConnection = cnn
cmd.CommandText = "Select * from Table1" 'The sql for your query
cat.Views.Append "qryName", cmd 'qryName = whatever you want to call your query

cnn.Close
cat = Nothing
cnn = Nothing

End Function
Edited by zilonox because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.