Excel to Access using VBA

[Guest]

Hello,

We are in the process of making cycle times to see how long it takes to
complete a task. We have all of our forms in Excel, so people can choose
which tasks that want to perform. When done, they click a button on Excel and
it generates a spreadsheet for them.

We have been assigned a new task to actually grab these numbers. I am
wondering if using Excel Automation to Access is the proper way to go about
this. What I was thinking is grabbing the test number and the start date from
Excel and place these in a Access table I have created called tblExcel. By
the way, all our generation is by Excel VBA.

Any advice is greatly appreciated.

 

[Guest]

I personally think using ADO would be better than automation. You don't
need to tie up resources or control an entire Access session, you can simply
access the database via ADO and use SQL to INSERT INTO your table (or even
modify the table, make new tables, etc as needed). See the recent post "Data
from Excel Range to existing Access Table" for further info

 

[Guest]

Great! Now I want to get the maximum value, like a DMax function. Can this be
done in ADO?

 

[Guest]

Yes, SQL has equivalent functions; the syntax would be
SELECT MAX([FieldName]) FROM [TableName] WHERE {CONDITION}
e.g.:
SELECT MAX([Commission Paid]) FROM [Widget Sales] WHERE [Salesperson]='Dales'
For details there are many online references to SQL syntax; a net search
will help you find a good site. Also, check out the ADO reference at
http://msdn.microsoft.com/data/DataAccess/
(updated for .NET but most of it would still apply to VBA using ADODB
reference)