From Excel, display MsgBox with ID from Access table

I would like to put together a macro in Excel that looks at an Access table and displays a MsgBox with the last(highest) ID number from said table.

The Excel spreadsheet would be located on end users computers in various locations.

The Access database is located at: "D:\Users\Henry\Desktop\Tracking.accdb" from all computers.
The table name is: "Tbl-Form" and the field name is "ID".

If you can provide pointers to examples, I would appreciate it.

Thanks in advance!!

You can use ADO to query database.

See link for example.
http://www.minnesotaithub.com/2014/0...with-vbscript/

Just like looping through the rows to find the last row, it is not really good practise to open a table and loop to the last record as in the example.

Better to sort the table descending by ID and select the TOP 1 record. Your real DBA will appreciate it if this gets moves to an serious DB

Please Login or Register  to view this content.

There's a sample DB in the attachment - extract to the same directory as the workbook this is test with. Remembering, if you open a new workbook it must be saved first as 'Application.Path' (used in the code) is empty until the WB has been saved.

CK76
Thanks for jumping in and giving me a hand!
I've modified the script you pointed out with my information, but only need the highest or last ID number entered.
I don't believe I need the looping, but don't know enough about it to be sure.

While using this code, I initially received the following error.

runtime error '-2147217900 (80040e14)'
On this line: Set rs = objConn.Execute("SELECT [ID] FROM Tbl-Form")

I placed Tbl-Form in brackets as [Tbl-Form] and am no longer receiving an error.

I have a msgbox popping up, but instead of displaying the last ID which should be TR0684, it says 228340.

Any thoughts on what that might be?

---

AB33
I certainly appreciate you popping in here!
I didn't want to add additional requests for something different to my other post, so I started this one.

In this case, I am not providing the ID number to Access, it is just incrementing automatically perfectly.
I also do not want the ID number from Access returned to a cell within Excel.

I would like a msgbox to open in Excel and display the full ID number from Access.

Ex: ID# TR0686

If I have misunderstood, I apologize.

What you gave me works great to return it to cell A2, but I am looking for a MsgBox.

---

cytop
Thanks for responding, but there are pieces of yours that I do not understand.
I am unable to download your example and apologize if the answer is inside.

For strPath you have \Database11.accdb
My database is at D:\Users\Henry\Desktop\Tracking.accdb
Would I just substitute it here?

At strConnection I assume I put my database location here at Data Source, correct?

I am just looking for the last ID entered, the highest in the series.
Do I need to loop or does the looping start at 1 and cycle through all numbers entil the last number?

Thank you so much for responding!

@elzool

Any thoughts on what that might be?

Not sure as I don't know what's in your DB. But as cytop indicated, you should sort query based on some field (be it ID or time stamp) and return Top 1 for [ID].
What you did was just grab everything from that [ID] column and getting first value in recordset.

cytop
Thank you for the response!

For strPath I have inserted the path to the database.
Would I leave strConnection as it is or change it to the same path?

As it is now, I am receiving an error on con.Open strConnection of Not a valid file name.

This is what I have now.

Please Login or Register  to view this content.

---

ck76
Gotcha, I will start working on that then. Thank you

This line.

Please Login or Register  to view this content.

You already hard coded path. But you are concatenating strPath on top of it.

I ended up up going with the example from AB33.
Thanks yet again AB33!!

I just added a line to display the MsgBox information.

Thanks for the assistance everyone!

Please Login or Register  to view this content.

elzool,
Sorry!
I left you in limbo. I was about to turn-off my PC when I saw the thread.
Here is another option-a one liner.

MsgBox ("ID No is " & rs.GetString())

AB33

That was perfect, thank you yet again!
Less is more and is quite elegant.