ms access 97 to 2003/excel dao syntax error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

ms access 97 to 2003/excel dao syntax error

Author

Topic

jleiker
Starting Member

13 Posts

Posted - 2004-10-21 : 09:08:20

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:="", _
This is password protected database now...didn't used to be.
It was inherited and have no idea of what to do.
I don't have any idea of what to do and this has to run every night.

CODE:

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
.Quit
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

K_Mueller
Starting Member

3 Posts

Posted - 2004-11-02 : 03:39:30

Hallo,
replace:
Dim db As Database 'used in traversal
Dim rec As Recordset 'used in traversal
with:
Dim db As DAO.Database 'used in traversal
Dim rec As DAO.Recordset 'used in traversal

and set a reference to the DAO Object Library

Klaus

jleiker
Starting Member

13 Posts

Posted - 2004-11-05 : 08:06:47

I did set the reference and that fixed it (excel object reference). Do you think I still need to replace the other code you suggested since it's working now?
Thanks so much for your reply!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-05 : 09:05:21

in Access versions > 97, if you just the DAO object library, ALWAYS prefix DAO objects with "DAO" to avoid confusion and because it is no longer the default library and ADO has many of the same object names.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources