An Example to learn from

[jeh]

Hi,

I know I'm asking alot here, but is there anyone out there that has a small Access data base that they use to get imformation from through Excel? I want to learn how to use Excel to display data from Access. What I'm most interested in, is how to get records that are sorted in access,and then displayed in Excel. I have tried to record a macro to do this, but if leves more questions than answers as to how to alter or manipulate the different fields that I want.

A bonus would be is an example on how to add records to the MDB from Excel as well.

I know its a tall order, but maybe by chance someone has something they could share.

Thanks so much for your time,

Jim

[lenze]

We don't use Access where I work, but we pull data into Excel from a huge Oracle database on a daily basis using MS Query. Most of us forgo the wizard and use the design mode. We can specify parameters and sort orders for the retrieved data.

[sen_edp]

Hello Jim

You have to go to Data->Get External Data->
New database query.
Choose data source: Ms access database
and choose the mdb file you want to import
,choose the fields, and the sorting method
and Voila !

[jeh]

Hi sen_edp,

Believe it or not, I was able to do that much. I guess my question about that is; do I have to import the whole table to get the data. I was hoping there would be a way that I could search for all names(from Excel) that start with "H" and it would return all the records that are associated with "H" from the MDB.
Thank you,

Jim

[lenze]

See the help file for "Parameters" and "Criteria". A parameter is a value the user is prompted for, such as records from a certain saleman. You enter the saleman's ID or name and the query retrieves only records of sales by that salesman. A criteria is is preset filter on your database, that filters out unwanted records, such as only open orders.

HTH

[ This Message was edited by: lenze on 2002-05-07 08:13 ]

[Damon Ostrander]

Hi jeh,

It is very easy using automation to transfer data directly back and forth between Access tables and Excel worksheets. Here is a very simple example I happened to have handy:

Sub ToAccess()

Dim axsApp As Object
'Assume Access already open with desired database
Set axsApp = GetObject(, "Access.Application")

Set cdb = axsApp.currentdb
Set rst = cdb.openrecordset("Employees")

Dim iRow As Long 'Excel worksheet row number

'Loop thru first 8 rows on active worksheet and put data from
'first two columns into Access employees table

For iRow = 1 To 8

With rst
.addnew
' read the names from Excel into Access table
!LastName = Cells(iRow, 2)
!FirstName = Cells(iRow, 1)
.Update
End With

Next iRow

End Sub

[jeh]

Thank you all for your help. I'm going to give it a shot.

Thanks very much for your time,


Jim