Module Problem

[kwooten]

I ran an "Analyze Performance" on one of my tables and it suggested to index a particular field in a table that is created by running an append module. Now that i have created this index I am getting corruption errors and when I select my module I get the error; "The module name 'modImportData' is misspelled or refers to a module that doesn't exist". Should I take off the index in the table or is there a way to redo the module to account for the index? It is a big table and want it to run as fast as possible when I run a query off it.

[kwooten]

Also, on this module, it is importing files which has about 86 columns which is slowing this DB down. Can I add something into the module to make it only pull the 5 or 6 columns I actually need?

[orange]

Without knowing more about your set up it seems you are selecting a lot more fields than necessary for your processing. You could (as most people do) use a query to select onlly the fields needed for processing.

A table with 86 columns sends up a flag to me. - Why so many columns? It may be ok, but is rare.
Show us your tables and relationships and post the code for your module if you want more specific advice/suggestions.

[kwooten]

The tables has 86 columns because the file I am importing has 86 files and the module imports the entire file. I could delelte the columns i dont need but that is a pain to do everytime. I did set up a query to select only the columns I need but with a file that is 86 colmumns by 220,000 rows is a bit excessive.

Sub ImportData()
Dim sFileDir As String
Dim fs
Dim fsFolder
Dim fsFile
Dim sFileName
Dim db As Database
Dim sSQL As String
Set db = CurrentDb
Set fs = CreateObject("Scripting.filesystemobject")
sFileDir = CurrentProject.Path & "\"
Set fsFolder = fs.getfolder(sFileDir)
For Each fsFile In fsFolder.files
sFileName = fsFile.Name
If Len(sFileName) = 11 Then
If IsNumeric(Left(sFileName, 6)) Then
If Right(sFileName, 5) = ".xlsx" Then
Debug.Print sFileName
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblImportedData", sFileDir & sFileName, True
sSQL = "UPDATE tblImportedData SET OriginalFilename = '" & Left(sFileName, 6) & "' WHERE (isnull(OriginalFilename))"
db.Execute sSQL
End If
End If
End If
Next
Set fs = Nothing
Set db = Nothing
End Sub

[orange]

I don't have acc2007, nor Excel 2007, but am looking at your code with 2003.

You should explicitly Dim your variables and assign a datatype.
I have included very basic error handling which I recommend you do.
Notice on the db.execute there is a second parameter - to show errors if they occur.

Here's is your procedure revised for readability and with Variables Dimmed with datatypes.

Sub ImportData()
Dim sFileDir As String
Dim fs As FileSystemObject
Dim fsFolder As Folder
Dim fsFile As File
Dim sFileName As String
Dim db As Database
Dim sSQL As String
   On Error GoTo ImportData_Error

Set db = CurrentDb
Set fs = CreateObject("Scripting.filesystemobject")
sFileDir = CurrentProject.Path & "\"
Set fsFolder = fs.getfolder(sFileDir)

For Each fsFile In fsFolder.files
    sFileName = fsFile.Name
    If Len(sFileName) = 11 Then
        If IsNumeric(Left(sFileName, 6)) Then
            If Right(sFileName, 5) = ".xlsx" Then
                Debug.Print sFileName
                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblImportedData", sFileDir & sFileName, True
                sSQL = "UPDATE tblImportedData SET OriginalFilename = '" & Left(sFileName, 6) & "' WHERE (isnull(OriginalFilename))"
                db.Execute sSQL, dbFailOnError
            End If
        End If
    End If
Next
Set fs = Nothing
Set db = Nothing

   On Error GoTo 0
   Exit Sub

ImportData_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ImportData of Module Module1"
End Sub

What else do you do with this data? That is what is the purpose of this ?

[kwooten]

I tried this one which imports the file from the host DBF file, which I export to excel.

Private Sub RunImportData()
Dim sFileDir As String
Dim fs
Dim fsFolder
Dim fsFile
Dim sFileName
Dim db As Database
Dim sSQL As String
Dim sSourceFile As String

Set db = CurrentDb
Set fs = CreateObject("Scripting.filesystemobject")
sFileDir = CurrentProject.Path & "\"
Set fsFolder = fs.getfolder(sFileDir)
For Each fsFile In fsFolder.files

sFileName = fsFile.Name

If Len(sFileName) = 10 Then

If IsNumeric(Left(sFileName, 6)) Then

If Right(sFileName, 4) = ".dbf" Then

Debug.Print sFileName

Link_DBF_Table "Dbase 5.0", "A:\Keith\Reports\HEADCOUNT\DBASE FILES", Left(sFileName, 6) & Right(sFileName, 4), Left (sFileName, 6)
DoCmd.TransferDatabase , "dBase 5.0", sFileDir, acTable, sFileName, Left(sFileName, 6)
sSQL = "INSERT INTO tblImportedData ( EmpNo, CCenter, Div1, Div2, Div3, Grade, OriginalFile )"
sSQL = sSQL & "SELECT EMPNO, CCENTER, DIV1, DIV2, DIV3, Grade '" & Left(sFileName, 6) & "' AS Expr1 FROM " & Left(sFileName, 6)
Debug.Print sSQL
db.Execute sSQL

sSQL = "DROP TABLE " & Left(sFileName, 6)
Debug.Print sSQL

db.Execute sSQL

End If

End If

End If

Next
Set fs = Nothing
Set db = Nothing
End Sub

[kwooten]

I don't have acc2007, nor Excel 2007, but am looking at your code with 2003.

You should explicitly Dim your variables and assign a datatype.
I have included very basic error handling which I recommend you do.
Notice on the db.execute there is a second parameter - to show errors if they occur.

Here's is your procedure revised for readability and with Variables Dimmed with datatypes.

Sub ImportData()
Dim sFileDir As String
Dim fs As FileSystemObject
Dim fsFolder As Folder
Dim fsFile As File
Dim sFileName As String
Dim db As Database
Dim sSQL As String
   On Error GoTo ImportData_Error

Set db = CurrentDb
Set fs = CreateObject("Scripting.filesystemobject")
sFileDir = CurrentProject.Path & "\"
Set fsFolder = fs.getfolder(sFileDir)

For Each fsFile In fsFolder.files
    sFileName = fsFile.Name
    If Len(sFileName) = 11 Then
        If IsNumeric(Left(sFileName, 6)) Then
            If Right(sFileName, 5) = ".xlsx" Then
                Debug.Print sFileName
                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblImportedData", sFileDir & sFileName, True
                sSQL = "UPDATE tblImportedData SET OriginalFilename = '" & Left(sFileName, 6) & "' WHERE (isnull(OriginalFilename))"
                db.Execute sSQL, dbFailOnError
            End If
        End If
    End If
Next
Set fs = Nothing
Set db = Nothing

   On Error GoTo 0
   Exit Sub

ImportData_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ImportData of Module Module1"
End Sub

What else do you do with this data? That is what is the purpose of this ?

Thanks. I take this data and create headcount reports. each file i pull in has headcount data for the month. I dont create the data otherwise I would do an append query. I need the 12-15 files loaded into 1 table.

[help_me_with_access]

I ran an "Analyze Performance" on one of my tables and it suggested to index a particular field in a table that is created by running an append module. Now that i have created this index I am getting corruption errors and when I select my module I get the error; "The module name 'modImportData' is misspelled or refers to a module that doesn't exist". Should I take off the index in the table or is there a way to redo the module to account for the index? It is a big table and want it to run as fast as possible when I run a query off it.

kwooten,

word to the wise...never, EVER let office automation guide your programming practices (unless you're getting code from a macro). that is the biggest mistake you can possibly make. office programs are NOT reliable development tools. if you are a developer with knowledge about accepted standards and protocols in the industry, do things yourself or through research only.

[kwooten]

kwooten,

word to the wise...never, EVER let office automation guide your programming practices (unless you're getting code from a macro). that is the biggest mistake you can possibly make. office programs are NOT reliable development tools. if you are a developer with knowledge about accepted standards and protocols in the industry, do things yourself or through research only.

Lesson Learned.. I got sick of messing with it and started over.

[help_me_with_access]

good for you. I wish you luck fellow developer...