Inserting last auto-generated ids

[Vbanoob98]

I have a linked table from excel which I refresh every month and export the data to another Access table. This table generates an autoid for each row which I copy and then paste back into the excel. After this I export the data with the ID to another Access table.

I was wondering if there was a way to autmate this to reduce user error. Thanks a lot

Excel linked table-->access table 1 (generates ID)--->back to excel linked table-->access table 2

 

[xenou]

This doesn't sound particularly error prone, although it does seem a little like a long-winded process. What is the point of creating these ID's? Why not create them directly in Excel instead of using this process to generate IDs in Access?

 

[Vbanoob98]

The people that would be using it dont know how to copy/paste with Ctrl+C,V.

Table 1 assigns an ID to the row information from excel (can be 1 row or 10k rows inserted with another method), then Table 2 assigns another ID to that row information. This is so data From Table 1 is grouped but still remains individual with the help of Table2

In excel I would just do a concatenate+vlookup and would get the required IDs but here in access I cant seem to find how. I have found similar threads but the answers dont work in this situation or seems too high level :/

I was hoping for an easy fix, although maybe there isnt

 

[xenou]

Maybe you could get this done on the Excel side. Perhaps with just the concatenat+vlookup formula you are talking about. It used to be you could not update Excel linked workbooks from Access. For that reason (and others) I generally prefer not to work with Excel linked workbooks as tables (except in rare cases, and then only as read-only tables).

 

[Vbanoob98]

But I cant do vlooukp from excel to access right?

I was wondering if there was a way to query the last inserted rows

So If I appended from my excel table to Table 1 10 rows, I could somehow query only those 10 rows, then I could use that to insert into table 2. Or something like that

 

[xenou]

A quick test shows me that it is not possible to insert or update a linked excel spreadsheet (which is what I thought was the case). So no, you could not use Table1 to insert into Table2 (assuming Table2 is a linked Excel spreadsheet).

 

[Vbanoob98]

I could convert the excel to a local table

 

[xenou]

If Table2 is a local table you can do inserts or updates or whatever you need to do, that is correct. I don't see any reason to use a second table - why not just do the updates in the table directly?

 

[Vbanoob98]

There is a form that is used to input the data in the two tables directly (Table 1 and 2), but its 1 by 1

Im trying to find a way to upload the data in batches so that it can be done faster.

If the excel table is local, how can I retrieve the autogenerated number from Table 1?

1. Data from the excel (local file)---->2.
Name, Amount, Address, Total

2. Table1 adds ID----->3.
Name, Amount, Address, Total, ID

3. Data from the excel (local file) recieves ID ----->4.
Name, Amount, Address, Total, ID

4. Table 2 adds second ID----->Done
Name, Amount, Address, Total, ID, ID2

 

[xenou]

The short answer is that if you don't know the id you will have to look it up by Name, Amount, Address and Total.

Select ID from Table 
    where Name = 'Olaf' and Amount = '10.00' and Address = 'Arendell' and Total = '100.00'

Overall this is a somewhat awkward workflow - you are mixing data in 3 places (two local tables plus an Excel table). That's probably just not the best structure to begin with - so not sure you want to stay on this path.