Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Load to Access (03)

    Edited by HansV to provide link to post - see <!help=19>Help 19<!/help>, it's very simple

    I have been toying with Rory's code from <post#=280250>post 280250</post#> and encounter this error message: "Number of query values and destination fields are not the same". Can someone explain what it means. I have attached a zipped file with an Excel workbook and access database sample.

    Thanks,
    John

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Excel Load to Access (03)

    John,

    It means that the number of values you are feeding to the access table do not meet the table requirements. In your example, your strsql statement is trying to feed 8 values into the first row of the table, which only can hold 4 values. To see this, in your code,
    insert debug.print strsql as shown below.

    strValues = Left(strValues, Len(strValues) - 2)
    strSQL = "INSERT INTO tblTable1 ( First, Last, Age, City ) "
    strSQL = strSQL & "VALUES (" & strValues & ");"
    Debug.Print strSQL 'Insert this here

    db.Execute strSQL
    db.Close

    Then after your run your macro and get the error, debug and open the immediate window. It will show you the strsql string trying to pass data to access as shown below:

    INSERT INTO tblTable1 ( First, Last, Age, City ) VALUES ("MyFirst1", "MyLast1", "21", "Albany", "MyFirst2", "MyLast2", "22", "Chicago");
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Load to Access (03)

    The code in Rory's example is intended to insert a single record into a table. Your selection consists of two records. You cannot insert them at once using this method, you'll have to loop through the rows.

    Another problem is that the table in the database is named Table1, not tblTable1 as in the code.

    The attached workbook contains code that loops through the rows and uses the correct table name. It works on my PC (after substituting the correct path)

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Load to Access (03)

    Hi John

    Small typo in the code:

    strValues = Left(strValues, Len(strValues) - 2)
    strSQL = "INSERT INTO Table1 ( First, Last, Age, City ) "
    strSQL = strSQL & "VALUES (" & strValues & ");"


    your code had tblTable1...enjoy
    Jerry

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Load to Access (03)

    Gary,

    Thanks for the response. I understand my selection was more than one row. If I just select a single row, it works.

    Regards,
    John

Posting Permissions

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