Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Posts
    2

    Unanswered: How do I run a make table in access via excel?

    Hi

    I am hoping someone maybe able to help with this. I currently have a make table set up in access and I am trying to run it from excel. I can pull data from the access database via excel but I cant figure out how to run the make table from excel. If indeed this is possible. Any ideas?

    Cheers
    DeadDuck

  2. #2
    Join Date
    Sep 2006
    Posts
    162
    You can link the tables if the data changes in ecxel and you want that to always be your data in table. Or you can import from a excel file.

    File --> Get external data

  3. #3
    Join Date
    Sep 2006
    Posts
    2

    cheers

    Hi Jay,

    thanks for the reply but I have managed to run the make table query by using the following code

    Set Db = DAO.DBEngine.OpenDatabase("db Name")
    Db.Execute "Qry_MakeTableName"

    I have changed this now to run a delete query and then an append query to first clear the table and then write the new data to it using the following code:

    Set Db = DAO.DBEngine.OpenDatabase("db Name")
    Db.Execute "Delete Query Name"
    Db.Execute "Append Query Name"

    Unfortunately I am now having trouble passing parameters to the Append query to stop it appending all the data. For example I am trying to limit the data by Country so that it only appends all of Austria's data into the table rather than the whole worlds. I have a cell in excel which has the country name in that I want to limit by, hopefully I will be able to work this out. Unfortunately I have to use excel as the front end and hide access.

    thanks for your feedback
    DeadDuck

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you use the docmd.openquery "Qry_MakeTableName" you don't need to delete the table first. The docmd.openquery automatically deletes the table if it exists while the Db.Execute command returns an error if the table exists. Also, try using the docmd.openquery instead of Db.Execute in your other commands as the docmd.openquery is more adaptable for changing parameters verses the Db.Execute. The Db.Execute is more designed for "static" and unchanging parameter queries. See if that works.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Mar 2006
    Posts
    163
    Why are you making a table in the first place?

    Couldn't you just use a normal select query to return the required data?

Posting Permissions

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