Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: An Example to learn from

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Washington State, USA
    Posts
    250
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 !



  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Washington State, USA
    Posts
    250
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Location
    Washington State, USA
    Posts
    250
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Thanks very much for your time,


    Jim

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •