Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is probably a common problem: I am trying to create an automated way to import an Excel spreadsheet into Access, but first:
    I have a simple test spreadsheet with columns: Company, FName, LName, Address, Event
    In my access database I have a TblCompany table. There is a primary ID key that is an autonumber field called: CompanyID, then: Company, FName, LName, Address, City, Province, PostalCode, Event.

    There are two problems: One, when I try to use the import feature and import the Excel data directly into the TblCompany, I get error messages and it will not import the data because of the primaryID key field (there simply isn't one in the Excel spreadsheet). I tried to just type numbers in myself in the Excel spreadsheet that are unique. It doesn't seem to work. Ah, but the real problem is with the Event field. You are probably wondering why I have an event field in the Company table. After lengthy discussions with my friend who I am creating this database for, she wanted to be able to have an easy way to import the client list that is provided to her each month already showing the event (eg. London Drugs). The company only ever has one event and after the event is over, the company gets archived and deleted from the database. Normally I would have the company connected to the Events table. Anyhow, there it is. The problem is though that the Event field is a number field that does show text in it, but a number because of the primary ID key field in the Events table. So, when I try to import the Event field from Excel (which is text) it does not allow me to.

    By the way, I tried experimenting using an Append query after I just imported the Excel spreadsheet into a new table called TblExampleImport. Using an append query is great because you can match the fields with the TblCompany table. The only thing is, I am trying to make this an automated procedure my friend because she would never remember how to import/append/etc.

    Sorry about the book here. I did try to figure this out for myself all night here. At least using the Append query, I don't have to have a primary ID key field in the Excel spreadsheet that was imported.

    Is there an easy way that I can do this?

    Thanking you in advance. I have received some incredible help from this site and I really do appreciate it ever so much.

    Kind Regards,

    Tina

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Apologies for the delay in getting a response - our Wizard HansV is on holiday, and the rest of us are struggling to fill his shoes. You have a couple of choices - you can either import the Excel workbook, or you can link to it and use it as the source for a query or whatever. And either approach can be automated using VBA - so you can specify a specific file to link to or copy into Access, or you can display a select file dialog box and let your friend select the file. Once you have done either, you can then use the Append query to put the data into TblCompany. Check out docmd.transferspreadsheet in the VBA help.
    Wendell

  3. #3
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Wendell,

    Thanks so much for your help. Yes, Hans is a huge help - quite the wizard.

    Cheers!

    Tina

Posting Permissions

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