0

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

Improve this question
9
  • I don't know why i got a -1 -.- Commented Jan 8, 2011 at 13:35
  • 1
    My advice is to use SQL Server Express. It's free, doesn't require people to have an Office license, will do what you want easily, and is clearly more powerful than Access. Commented Jan 8, 2011 at 19:39
  • Thanks @IanC , I really appreciate your suggestion but the kind of software that i developed is not a regular database-driven software. Often my client need to move the whole software from a computer to another and obviously he cannot lose his data. So i thought the best way to satisfy his needs is to use a file based database that can easily stored on a mobile device. I also tought to use an external online database, but my client is not always connected to internet. So i had no other choice than a file based db... Commented Jan 9, 2011 at 9:17
  • 1
    You're welcome. In that case, why don't you look at SQL Lite? It is designed for mobile, yet scales well for larger loads. And it's free, with source code even. Perhaps it can better fit your needs. Commented Jan 9, 2011 at 10:16
  • 1
    It sounds to me like a cloud solution would also fit. What many developers do is use SQL Lite on the client side, and a larger RDBMS on the server. For example, SQL Lite is natively supported by Adobe AIR, so you can store temporal data locally, and store what you need on the server (aka cloud). Commented Jan 9, 2011 at 10:18

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.