asked on

Win 7 - switching from XP - any code issues

Experts, I have switched from XP/Access 2007 to Win7/Acces 2010.
I import excel data on a regular basis and for some reason the import doesnt work as it should.
I am wondering if the below code (showing only partial code) should be updated to reflect the switch in operating system?

Maybe there are path differences between Win7 and XP but in addition is there anything else in the below that I need to change due to the switch? I am trying to figure this out by process of elimination and first need to know if there are other codes to update besides the path. thank you

       CurrentDb.Execute "delete * from [Import-CSM2]"

    Set xlApp = New Excel.Application
    With xlApp
        .Visible = False
        Set xlWB = .Workbooks.Open("C:\Documents and Settings\AJ\My Documents\Work\Company\Banks\CSM\Outstanding Reports (by tab).xlsm", , False)
        strName = xlWB.Sheets(1).Name
    End With
    Set xlWB = Nothing
    Set xlApp = Nothing
   
      DoCmd.TransferSpreadsheet acImport, , "Import-CSM2", "C:\Documents and Settings\AJ\My Documents\Work\Company\Banks\CSM\Outstanding Reports (by tab).xlsm", True, strName & "!"
    DoCmd.OpenQuery "qryUpdateCSM2_SovToSant"

Open in new window

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pdvsa

ASKER

Thank you. I believe the issue is with the data. Maybe the dates i importing are not "real" dates and those records are not imported.

peter57r

Well my impression , without any hard evidence, is that it is getting more difficult to import data from Excel into Access and that more data is now being rejected because it is the 'wrong datatype' . There have always been issues of this nature, but as I say, I just feel it is getting harder to get compatibility.

I have a firm but apparently wrong view that we used to be able to create a table in Access of all text fields and then you could import anything from Excel into that table. This might be a quite false memory, but it's now not possible to use such a technique because the import requires matching datatypes. And since Excel doesn't really have datatypes, it means you have to wait until Access works out what it thinks each field is and then try and fix it.

pdvsa

ASKER

Peter: that seems like the issue I am having. When I import, it gives me a mismatch of datatype error and I click ok and it continues to import but doesnt import all data. The only big change was moving to 2010 platform and win 7. The data should be the same. Oh well I guess I have to deal with it or rework the import. It wil be tedious.

thanks for your thoughts....

pdvsa

ASKER

ahh....I just figured it out. My field, formatted as text in the access table, was being populated with a some records that were number. I did a text to columns in excel and formatted the column as text and imported. All data imported after converting the column to text to match the field property in the table. This was not an issue before switching to 2010. I am pretty certain.

I feel better now.

pdvsa

ASKER

also, I did not have to change the path name to the win 7 path. it worked with the XP path.