VS 2012 exp + MS Access + TableAdapter Insert Question - Get AutoNum value?

[Static]

First off Hey!!! been a while... lol

Ok, my wife owns a flower shop.. and she wants a POS system, we tried MANY of them (even ones that are for flower shops) and well, they are either too "generalized" for all retail, or if they are for a flower shop, they are terrible!
(I found one that cost 2500 and had so MANY quality issues and errors that i dont understand how people think its worth even $1!!!) - ok.. back story done.

Heres my issue/question.

in Access I have tables
Customers
Orders
OrderDetail
SendTo

(also Items and category - but not important)

so, the order has been taken, all info filled in etc etc...
Now she hits save (or whetever) and i need to write all the parts in...

INSERT Customer IF NEW - GET CUST ID back?? < so if its a new customer and it inserts it, how do i get the AutoNum back to then insert into the Orders Table
Got CUST ID...
INSERT Order INTO ORDERS < again.. this will have a new ORDER_ID how do i get that back?
etc etc

basically when i insert a new item into a table, i need to pull the new ID number to use in other tables so they are all linked.
What is the best way to do this? (Its been a long time (like vb6 long time) since i have created this kind of project so be nice!! lol)

I have some table adapters set so i can pull info like items and categories to put into the order, but thats all so far

Thanks!


(Side note question: in Vb.net 201x - if i make a change to the a table/field in Access, how do i get Vs to see that? thanks)

[ForumAccount]

MAX(AutoNumberColumn) should work. I know that (unless it has changed) there is no @@IDENTITY or SCOPE_IDENTITY() equivalent in Access, however the seed used by the table should always increase and even failed inserted records should increase the seed. Therefore, the latest insert should always be the highest value in that column.

[Static]

So, there is no way to grab on insert? i would need to

INSERT Customer
SELECT MAX(CustomerID)
INSERT Order
SELECT MAX(OrderID)
INSERT OrderDetails

etc???

that seems really inefficient, and if the customer base gets to be large... then i could see this slowing it down.
What if i used MySQL instead?


another side question: Should I be using tableadapters?? (no such thing in VB6 lol)

[dunfiddlin]

That's the price you pay for transactions which are direct to the database. If you insert to dataset tables then you can update bound controls and the database simultaneously. If you are using databound DGVs as your editor then you can additionally update in batches rather than on single records if that helps efficiency.

[Static]

was that in english dunfiddlin? lol

There wont ever be massive amounts of data being pushed in... and i dont like to bind controls (i like to control what is seen)
I am using a simple listview control to show the items on the order...her shop is not big so a typical order will have maybe 3 items on it.

[ForumAccount]

To answer a couple of your side questions:

1) I (personally) wouldn't use MS Access. MySQL is compatible with the Entity Framework.
2) I'd probably use the Entity Framework for this application. TableAdapters and the like are really old.
3) Yes, you'd probably have to do those MAX(Column) queries (which you'd wrap into your insert functions).

[Static]

Ok.. Entity Framework?

and TableAdapters are old? hmm ok (lol new to me!)

ok so im going to guess ADO then..
and MySQL... do i need to do anything fancy to install locally?

Thanks!

[kareninstructor]

In the Code bank section jmcilhinney has an example of how to get the new row identifier

[Static]

ok, so i got MySQL Installed and started one table to test.. i was able to connect to it and load a combobox to it... i also noticed its faster (even with 3 rows of data, the Access connection stalled on the first fill of the table adapter)

great.. so the only real question i have left at the moment is, once this is all done... how do i distribute a MySQL db?? with access i could just drop it on her PC. Do i need to do the whole server install? like i did for this? (Or, is there a simple server install where i can just use code or import the tables etc...)

Thanks!