Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004

    Unanswered: upgrage to 2003 and DAO syntax error?

    Please help?! We just upgraded from 97 to 2003 version. This is a scheduler routine which auto imports an excel spreadsheet into our main db. After I upgraded it stops on with "compile error variable not defined" Also after the conversion ...there was an error that said "May be due to old DAO syntax that is no longer supported" l am pasting the module code below but this line is where I get the error:
    FileFormat:=xlexcel9795, Password:="", WriteResPassword:="", _

    I don't have any idea of what to do and this has to run every night.


    Public Function importisfile(imptype As String)
    Dim importpath As String 'path for work or home
    Dim impfile As String 'filename of xl import
    Dim tempfile As String 'temporary for rewrite
    Dim tblimptemp As String 'tablename
    Dim stdocname As String 'conversion string
    Dim stdocname1 As String 'conversion string
    Dim recsetname As String
    Dim XL As Object
    Dim db As Database 'used in traversal
    Dim rec As Recordset 'used in traversal
    Dim tbltempstr As String 'table to traverse
    Dim x As Integer 'used to count traversal

    'importpath = "V:\Administration\Imports\"
    importpath = "V:\Administration\Imports\"
    impfile = importpath + "TOX" + "kit.xls" 'eg. c:\import\TOXCANkit.xls
    tempfile = importpath + "TOX" + "kit2.xls" 'eg. c:\import\TOXUSkit2.xls
    stdocname = "qryappendimportTOX" 'date conversion in this query
    stdocname1 = "qrydeleteimportTOX" 'date conversion in this query
    tblimptemp = "tblimporttempTOX" + "Kit" 'eg. tblimporttempTOXCANKit
    tbltempstr = "tblTOX" + "kit"

    On Error GoTo Err_imp1

    Set XL = CreateObject("Excel.Application")
    With XL.Application
    .Visible = True
    .Workbooks.Open impfile
    .ActiveWorkbook.SaveAs FileName:=tempfile, _
    FileFormat:=xlexcel9795, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    End With
    Set XL = Nothing

    Kill impfile
    Name tempfile As impfile

    'transfers the readable xls file into access database
    DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel97, tblimptemp, impfile

  2. #2
    Join Date
    Jul 2004

    Forgot to add something

    We have not converted the backend tables to 2003 yet.....still in 97 format.

Posting Permissions

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