CODEWITHSUNDEEP
sqlvb.netms-access
Luciano Mammino
Luciano Mammino

Reputation: 811

Creating stored queries programmatically with vb.net, odbc and access

i have developed a small ms-access based software with vb.net.

I've added auto-update capabilities to the software (mostly by using clickonce) to simplify the release of new features.

Every version of the software executes the update routine which may update also the existing database. Lately i've made few changes on the database structure adding few stored queries, so i want the autoupdate code to programmatically add these new queries to the existing database and make it perfectly up to date.

I haven't already found a solution to add stored queries to an ms-access database using odbc... I also tried to use the "CREATE PROC" sql statement but it does not seem to work with access databases, even if i create the query form the Microsoft Office Access front-end. I've found some examples that uses ADODB, but i'm using odbc to remain both x86 and x64 compliant.

PS: sorry for my bad english... I hope i've been clear enough

Upvotes: 0

Views: 1283

Answers (2)

RolandTumble
RolandTumble

Reputation: 4703

If you can set a reference to DAO (which is actually much "closer to the metal" of Jet/ACE than ADO is), check the CreateQueryDef method and the QueryDefs collection.

Upvotes: 1

David-W-Fenton
David-W-Fenton

Reputation: 23067

Stored queries in Jet/ACE are of two types, SELECT queries and what Access calls "Action" queries. SELECT queries correspond to VIEWS and action queries to SPROCs. So, if it's a DML statement, you'd create it as an SPROC, while if it's a SELECT, as a VIEW.

The one thing I'm not sure of is how parameters interact with this. I don't use Jet/ACE except from Access, so this is not something that I'm experienced with doing, so don't really have the answer for that.

Upvotes: 1

Related Questions