Anyone use Access?

[bdi]

Maybe it's because you just can't do it, or maybe it's because of my tendency to avoid Access when possible, but I figure I'll give it a shot anyway.

I want to setup an Append Query, and I want to return the key (autonumber field /primary key) from the row that's added. I don't see how I can return a value from an append query though. Anyone know of a way this can be done?

I'm thinking about creating a module and just calling 2 different queries within it. If anyone knows a better way though, I'd still appreciate hearing it.

[bdi]

Just so it's clear what I'm doing, I am converting a SQL Express database to Access 2000. In the SQL stored procedure I have an insert statement followed by a select for the last ID generated. My application calls it by the stored procedure name. If I go the module route, I doubt I will be able to keep my current code in the application, so I am hoping I can do this as a query.

Maybe I can create a query that is just SELECT MyFunction(<parameters>)
That function could run the instert, and then run a select on the max ID returning it to the query, and the query could return it to the app. That would probably get me what I need. Now I just need to figure out if Modules are stored in the MDB file.

[bdi]

In case anyone had any interest in this, I figured out that I could call a query and have that query call a function that ran my actual insert and then ran a select to get the ID. The problem with that was when *narf* brought up that by doing that I am adding ADO and MDAC to my list of technologies being used. I made the business decision that adding the extra overhead would be worse in the long run by creating more support issues and possibly adding to the install. Therefore, I am just going to call the queries individually from the application.

Luckily I took a tiered approach to the application and it looks like most of the insert queries are centralized and will be easy to add the additional line to.

[xardoz]

Yes, very interesting!

[bdi]

Well, being that someone is actually reading this, maybe I'll post some more stuff I came across (although, it's probably just as boring as the previous posts).

First with ADO.NET I found that the SQL Data Provider and the OleDb Data Provider treat parameters differently. It seems that while SQL can use named parameters (@Key...) and while when you setup an OleDbParameter you can still enter that name, it gets ignored. Instead you have to enter the parameters in the order they appear in the query.

You can also use a question mark ? as a placeholder rather than typing out a variable name.

If I had known this before I started, I would probably have worked my SQL queries into my ADO.NET code rather than setting them up in Access. I think the advantage of being able to see the query and the order I need the parameters would outweigh the benefits of putting the queries in the Access database. The advantages to SQL were the stored procedures being precompiled for better performance, and the security abilities. I can duplicate the security abilities, and I'm not sure if the performance is different in Access or not.

[bdi]

hum dum dee dum....

Guess you can use stored procedures in Access too, although you can't get to them through the Access User Interface:

http://www.devcity.net/Articles/18/msaccess_sp.aspx