Excel Analysis, to Access Database

[SuddathAL]

I use an excel spreadsheet to run a financial anaysis of selected orders. If the result is positive, I "take" the order. I use this sheet dozens of times a day and save each one as its own file. Is there a way to export the results of the analysis into an Access database. My vision of this system is using the spreadsheet to run the analysis and just hitting a "submit" type button that shoots the results over to Access. I think the key will be getting this to create a new record, with all of the data in the proper field.

Any help will be appreciated.

 

[Montez659]

You might want to post this under the Excel forum, though I am sure that someone here can help.

I cannot remember which libraries are in Excel for you to use, but here are a few things you will want to check out.

This is a code snippet that I recently used to access a remote database. This should work in Excel, but like I said, I don't know about the libraries:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim conString As String
Dim strSql As String
        
conString = "\\Lit-tzg-file-01\Public\yourDatabase.accdb"

Set db = DBEngine.Workspaces(0).OpenDatabase(conString)

strSql = "SELECT * FROM [tblUsers]" ' add UPDATE statement here
Set rs = db.OpenRecordset(strSql)

db.Close
MsgBox (passString)

I used my code to merely look up a value in a remote db, but all you would have to do is add an SQL UPDATE string. Do a search for "SQL Update, vba", and you should come up with what you need to put in the middle.