By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,057 Members | 1,346 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,057 IT Pros & Developers. It's quick & easy.

naming an Access error log file following import using VB function

P: 1
I want to import a large number of Excel spreadsheets that are 'supposed' to have the same number of columns representaing the same type of data into tables in an Access database. The UNC path and filename are stored in an Access table ("chemoabstract"). I run the following function, which searches the table for a matching file ID and hopefully imports the data into a file named whatever MYFILEID is. What I would like to do is flag up instances of import errors and rename the error log files so that I can identify them appropriately, i.e MYFILEID_ImportErrors. All ideas gratefully received.

Function ImportChemoSSheets(MYFILEID As String)
'Import Spreadsheets
'MYFILEID = filename (e.g. 100_1)
'[Filename] = path e.g. C:\MyDocuments\
'[Extension] = file type = ".xls"
Dim MyDB As Database 'Current database
Dim MyTbl As Recordset
Dim strUNC As String
Dim strTableName As String
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyTbl = MyDB.OpenRecordset("Chemoabstract")

If MyTbl.BOF And MyTbl.EOF Then
MyTbl.Close
Set MyTbl = Nothing
Exit Function
End If

MyTbl.MoveFirst

Do While Not MyTbl.EOF
If MYFILEID <> MyTbl![FILEID] Then
MyTbl.MoveNext
Else
strTableName = MyTbl![FILEID]
strUNC = MyTbl![FileName] & MyTbl![FILEID] & MyTbl![FileExtension]
DoCmd.TransferSpreadsheet acImport, 8, strTableName, strUNC, True, ""
MyTbl.MoveNext
End If
Loop
MyTbl.Close
Set MyTbl = Nothing
Set MyDB = Nothing
End Function
Oct 27 '06 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.