An Example to learn from

jeh

Active Member
Joined
Apr 27, 2002
Messages
250
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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.
 
Upvote 0
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 !
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Thank you all for your help. I'm going to give it a shot.

Thanks very much for your time,


Jim
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top