Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316

    Unanswered: TransferSpreadsheet to non-linked table

    Is it possible to use DoCmd.TransferSpreadsheet into a table in a separate, non-linked database?

    Something like this:
    Code:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        "F:\BackEnd\db_tables2.mdb\tblCustomerDetailsTemp", strSourceLoc, True, ""
    The reason I need to do this is because our Access front-ends are packaged onto a remote server, but the packaging process does not allow for any tables to exist in the front-end. When I create a table in the backend (and link to from the front end) and attempt to import the Excel data into the linked table, I get a Numeric Field Overflow error, yet when I have the exact same table placed in the front-end, the import works without error. The only way I can think of to get round this is to somehow import the data into a non-linked table.

    Even when I put the import code into the backend, and call it from the front-end (via References) I still get the Numeric Field Overflow error!
    Last edited by bcass; 05-24-07 at 14:31.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    The numeric Field Overflow error you get might be because of bad/mixed column data in the spreadsheet. I used to get these kind of errors if there was a column which has mostly dates but a few values were like: 01/05 or had some text. MSAccess likes to try and intepret what the column type is by the values in the column (I think the first row) and this caused problems with mixed data in a column. You may want to create an Import Specification and use that in your importline code just to make sure or look closely at the data in the spreadsheet. If you have a date column in the spreadsheet, I bet anything that you have a value like 02/06 instead of 02/21/06 somewhere or a column with mostly numbers but a few text values.
    Last edited by pkstormy; 05-26-07 at 21:44.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    I knew about the import specification, but it's an Excel file I'm trying to import, and when I go to File > Get External Data > Import, and select the Excel file, the Advanced button isn't showing, so I can't create an Import Specification for it. Besides, that still doesn't explain why I only get the error if I import into a Linked table, but get no error if I import into a local table.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by bcass
    I knew about the import specification, but it's an Excel file I'm trying to import, and when I go to File > Get External Data > Import, and select the Excel file, the Advanced button isn't showing, so I can't create an Import Specification for it. Besides, that still doesn't explain why I only get the error if I import into a Linked table, but get no error if I import into a local table.
    The good news is we have this exact "works locally not with linked tables" problem before. The bad news I can't remember when or what the solution was (I don't actully use docmd.transfer.... so it did not stick).

    Have you tried searching the forum for the key words? The search actually appears to work nowadays....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    I have done a search, and so far, all I could find was the suggestion to either convert the Excel file to a text file (so I could then use an Import Spec), or to create an Excel object to process the data one row/field at a time. That would slow down the import process significantly (it's already quite slow as I do a lot of processing via ADO once the data is in the database), and there are all sorts of other issues/concerns about doing it that way.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'm still betting you got some values not consistent with the field type it's recognizing it as (i.e. 03/07). But I'm just a betting type of person.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - another suggestion. Transfer it locally and then INSERT into the remote table using a heterogeneous query (see the entry from M Owen in code bank for an example). Further suggestion: it may be possible to create a direct hetergeneous query with the excel sheet as the source... but I can't remember ever trying that using Access.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    I can't use any local tables. Strange rule, I know, but it's a fundamental requirement before the application is packaged.

  9. #9
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Quote Originally Posted by pkstormy
    I'm still betting you got some values not consistent with the field type it's recognizing it as (i.e. 03/07). But I'm just a betting type of person.
    Yes, that is exactly the problem, which is why I need to use an Import Specification. Even when I set all fields in the destination table to Memo, the error still occurs. The problem is a HouseNumber column. Sometimes it contains something like '24' and other times it might contain '24B', which causes ambiguity since it can sometimes be interpreted as a string, and other times a number. It's odd that it won't even put it into a Memo field though...

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Maybe some help

    I wouldn't recommend a Memo field but instead a text field. Memo fields can be difficult to work with. Sometimes if you specifically format the spreadsheet column to the data type in the source xls file, that helps. Also, blank lines, special formatting, and "Heading" notes are killers for spreadsheet imports.

    I have a vb script I've attached which clones and renames the excel file to a csv extension (you can change it to a txt). Maybe you can execute the vbscript in your vba code and then do the import which will then utilize an import specification. I haven't fully tested this out and you'll need to tweak the vb script in notepad and change the location (LUName) and file name. But overall it should work ok with a few tweaks. Otherwise, I guess you don't want to continually resave the excel sheet to a csv/txt file.

    Also, have you tried importing it with the docmd.transfertext and use the spreadsheet.xls name? Not sure on this - never tried it but it may work and you would be able to use an Import Specification. You may have to do it with a converted to csv/txt first on the spreadsheet just to create the Import Specification.

    There's also code you can write to import the xls file verses using the docmd.transferspreadsheet command but it might take a while to write.

    Good luck and let me know how it works for you.
    Attached Files Attached Files
    Last edited by pkstormy; 05-28-07 at 23:51.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    how did you get on with Paul's solution?

    i dug up my notes from the last time we came across this:
    Code:
     SELECT [MySheet$].Title AS Expr1, [MySheet$].TheDate AS Expr2
    FROM [MySheet$] IN '' [Excel 5.0;HDR=YES;IMEX=2;DATABASE=C:\MyFolder\MySheet.XLS;TABLE='MySheet' $];
    That is how to get data from excel. I think you can use that in an hetergeneous query.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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