Code to import spreadsheets

[Mark]

Hi,

i have an old piece of code that i have used before to
import spreadsheet ranges into access table, that loops
through the records in a table to bring in all of the
different ranges.

when i run this it tells me that database is now a user
defined type. i have read the help but i dont really
understand or it why database is no longer a type. any
help would be much appreciated.

this is the code:

Dim myDB As Database
Dim myrecset As Recordset

Set myDB = CurrentDb()

DoCmd.OpenQuery ("Delete DATAALL")
Set myrecset = myDB.OpenRecordset("workbooks",
dbOpenDynaset)

Do Until myrecset.EOF = True
DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel97, "DATAALL", "O:\inventory\manual
forms\" & myrecset![WORKBOOKNAME] & ".xls", -
1, "datadeliv"

myrecset.MoveNext

Loop
End Sub

 

[John Nurick]

Hi Mark,

Make sure that the DAO library is listed in your database's references
(Tools|References in the VB editor), and disambiguate the declarations:

Dim myDB As DAO.Database
Dim myRecset As DAO.Recordset

Hi,

i have an old piece of code that i have used before to
import spreadsheet ranges into access table, that loops
through the records in a table to bring in all of the
different ranges.

when i run this it tells me that database is now a user
defined type. i have read the help but i dont really
understand or it why database is no longer a type. any
help would be much appreciated.

this is the code:

Dim myDB As Database
Dim myrecset As Recordset

Set myDB = CurrentDb()

DoCmd.OpenQuery ("Delete DATAALL")
Set myrecset = myDB.OpenRecordset("workbooks",
dbOpenDynaset)

Do Until myrecset.EOF = True
DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel97, "DATAALL", "O:\inventory\manual
forms\" & myrecset![WORKBOOKNAME] & ".xls", -
1, "datadeliv"

myrecset.MoveNext

Loop
End Sub