Options for pulling data into excel

[SilverSN95]

I would like to be able to pull in data to excel from an access db, but I want to be able to easily specify where the database is located and named since this is likely to change. This data would then fill in fields in the excel form like Name, policy date etc.
My idea was to write a simple script that would use the db and table specified in two fields built in the excel file. I would then pull the record I want (specified by a number the user enters in the form) into another sheet in the wb, and have excel formulas display the name and date where it should be on the main sheet.
I suppose the other option would be queries, but when trying this I couldn't figure out how to change the name/location without throwing off the queries.

I'm new to this whole thing, so I was just hoping for some advice on an appropriate solution. Thanks, -Ben

[JimmyTheHand]

DAO and ADODB are two ways to access your DB. DAO is (I think) simpler, ADODB is (I think) more advanced. I'll give you some tips regarding DAO.

1st of all you need to set a reference to Microsoft DAO 3.x Object Library.
Then open the DB.
Dim DB As DAO.Database
DB = DAO.OpenDatabase("C:\myDatabase.mdb") But that's something you know well, as shown in your other thread. Now, you need to open a Recordset. This can be either a full table:
Dim Rst As DAO.Recordset
Set Rst = DB.OpenRecordset(myTableName) or the result of a query:
Dim Rst As DAO.Recordset, mySQL as String
mySQL = "SELECT [ID], [Field1], [Field2], [Field3] FROM [Table] WHERE [ID] = 123
Set Rst = DB.OpenRecordset(mySQL)
If the recordset contains only one record (with a query you can achieve this) that's just fine.
If the recordset contains several records, you can use the Filter property to define a subsequently opened (filtered) recordset, or the Seek method to find the record you want.

Then you simply extract the info from the record. E.g.
With myRecordSet
Range("A1") = ![Field1]
Range("A2") = ![Field2]
End With

IMO the simplest way to do what you want would be something like this:

Dim DB As DAO.Database

Sub test()
Dim Rst As DAO.Recordset
Dim Id As String
DB = DAO.OpenDatabase("C:\myDatabase.mdb")

Id = InputBox("ID = ?")
Do While Id <> ""
Rst = myRecordset(Id)
With Rst
Range("A1") = ![Field1]
Range("B1") = ![Field2]
Range("C1") = ![Field3]
.Close
End With
Id = InputBox("ID = ?")
Loop
End Sub

Function myRecordset(Argument_ID As Long) As DAO.Recordset
Dim mySQL As String
mySQL = "SELECT [ID], [Field1], [Field2], [Field3] FROM [Table] WHERE [ID] = " & Argument_ID
Set myRecordset = DB.OpenRecordset(mySQL)
End Function

Jimmy

[SilverSN95]

Thanks, I ended up doing something quite like this. Is the ! as in "![Feild1]" just refer to the value at that field?

[JimmyTheHand]

Is the ! as in "![Feild1]" just refer to the value at that field?
The expression below retrieves the value of the field called "Field1" of the current record.
Rst![Field1] In this syntax you use the actual field name, hardcoded. E.g. Rst![FirstName], Rst![Department_ID], etc.

If you want to have a variable as fieldname, you need to use a different syntax:
Dim FieldName as String
Rst.Fields(FieldName) Notice the "." instead of "!".

[andrewhay]

This help is very appreciable, I was looking for this all across the internet.