Import Specs Not Working 1

[shelby55]

Hello

I'm using Access 2003.

With the help of many on this site, I created a button on a form that will allow the user to open up to the folder they wish to get a text file from. The data is saved to a temporary table and if the data is new, it is appended, and if the record exists then the new record overwrites the old. The contents of the temporary table are deleted once the updates/additions are run.

The problem is that the import specs do not appear to be working as created. They were written to indicate that the first row does not contain headers and when I import manually using these specs they are correct and all records are included. But if I import via this import data button, the initial record is not imported...why would that be?

Below is the code for the button:

Private Sub cmdImportData_Click()
On Error GoTo Err_cmdImportData_Click
    'Declare a variable as a FileDialog object.
    Dim fd As FileDialog
    Dim strTableName As String
    Dim strSQL_T As String
    Dim strSQL_Q1 As String
    Dim strSQL_Q2 As String
            
    If MsgBox("Navigate to your data file. Please press 'Cancel' if you do not wish to proceed", _
            vbInformation + vbOKCancel, "Raw Data Import") = vbCancel Then GoTo Exit_cmdImportData_Click
 
 DoCmd.SetWarnings False
    
strTableName = "tmpData"
strSQL_T = "DELETE tmpData.* FROM tmpData"
strSQL_Q1 = "INSERT INTO FLO_Data ( Inst, ChartNo, AcctNo, Age, Sex, Rescode, Postal, AdmDate, AdmTime, AdmDateTime, Ifrom, Entry, Admit, DisDate, DisTime, DisDateTime, Ito, AcuteDays, ALCDays, TotalDays, MOHQ, CMG, CMGDesc, Fyear, Exit, Disp, InstName )" & _
"SELECT tmpData.Inst, tmpData.ChartNo, tmpData.AcctNo, tmpData.Age, tmpData.Sex, tmpData.Rescode, tmpData.Postal, tmpData.AdmDate, tmpData.AdmTime, tmpData.AdmDateTime, tmpData.Ifrom, tmpData.Entry, tmpData.Admit, tmpData.DisDate, tmpData.DisTime, tmpData.DisDateTime, tmpData.Ito, tmpData.AcuteDays, tmpData.ALCDays, tmpData.TotalDays, tmpData.MOHQ, tmpData.CMG, tmpData.CMGDesc, tmpData.Fyear, tmpData.Exit, tmpData.Disp, tmpData.InstName " & _
"FROM tmpData"

strSQL_Q2 = "UPDATE tmpData INNER JOIN FLO_Data ON (tmpData.AcctNo = FLO_Data.AcctNo) AND (tmpData.ChartNo = FLO_Data.ChartNo) AND (tmpData.Inst = FLO_Data.Inst) SET FLO_Data.Inst = [tmpData]![Inst], FLO_Data.ChartNo = [tmpData]![ChartNo], FLO_Data.AcctNo = [tmpData]![AcctNo], FLO_Data.Age = [tmpData]![Age], FLO_Data.Sex = [tmpData]![Sex], FLO_Data.Rescode = [tmpData]![Rescode], FLO_Data.Postal = [tmpData]![Postal], " & _
"FLO_Data.AdmDate = [tmpData]![AdmDate], FLO_Data.AdmTime = [tmpData]![AdmTime], FLO_Data.AdmDateTime = [tmpData]![AdmDateTime], FLO_Data.Ifrom = [tmpData]![Ifrom], FLO_Data.Entry = [tmpData]![Entry], FLO_Data.Admit = [tmpData]![Admit], FLO_Data.DisDate = [tmpData]![DisDate], FLO_Data.DisTime = [tmpData]![DisTime], FLO_Data.DisDateTime = [tmpData]![DisDateTime], FLO_Data.Ito = [tmpData]![Ito], FLO_Data.AcuteDays = [tmpData]![AcuteDays], " & _
"FLO_Data.ALCDays = [tmpData]![ALCDays], FLO_Data.TotalDays = [tmpData]![TotalDays], FLO_Data.MOHQ = [tmpData]![MOHQ], FLO_Data.CMG = [tmpData]![CMG], FLO_Data.CMGDesc = [tmpData]![CMGDesc], FLO_Data.Fyear = [tmpData]![Fyear], FLO_Data.Exit = [tmpData]![Exit], FLO_Data.Disp = [tmpData]![Disp], FLO_Data.InstName = [tmpData]![InstName]"
    
Application.SetOption "Confirm Action Queries", False
Application.SetOption "Confirm Record Changes", False
    
    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    Dim vrtSelectedItem As Variant
    Dim strFileName

    'Use a With...End With block to reference the FileDialog object.
    With fd
            
         'Do Not Allow the selection of multiple file.
        .AllowMultiSelect = False

        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the action button.
        If .Show = -1 Then

            'Step through each string in the FileDialogSelectedItems collection
            For Each vrtSelectedItem In .SelectedItems

                'vrtSelectedItem is a String that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                DoCmd.Hourglass True
                DoCmd.TransferText acImportDelim, "3M_Import_Specification", strTableName, vrtSelectedItem, True
                DoCmd.Hourglass False

            Next vrtSelectedItem
        'The user pressed Cancel.
        Else
            GoTo Exit_cmdImportData_Click
        End If
    End With

    'Set the object variable to Nothing.
    Set fd = Nothing
    
    'If DCount("*", "tmpData") > 0 Then DoCmd.RunSQL strSQL_T
    If DCount("*", "qryUnmatched") > 0 Then DoCmd.RunSQL strSQL_Q1 Else
    DoCmd.RunSQL strSQL_Q2
    DoCmd.RunSQL strSQL_T
    'DoCmd.SetWarnings True
      
    Application.SetOption "Confirm Action Queries", True
    Application.SetOption "Confirm Record Changes", True

Exit_cmdImportData_Click:
    DoCmd.Hourglass False
    Exit Sub

Err_cmdImportData_Click:
    DoCmd.Hourglass False
    MsgBox "Import of raw data was not successful", _
            vbCritical + vbOKOnly, "Raw Data Import Failed"

    MsgBox Err.Description
    Resume Exit_cmdImportData_Click

End Sub

Any and all assistance greatly appreciated.

 

[Andrzejek]

I would first check your SQLs. Your strSQL_T and strSQL_Q1 seam to be OK, but your strSQL_Q2 requires a lot of changes:

strSQL_Q2 = "UPDATE tmpData [...] SET FLO_Data.Inst = [blue]'" &  [tmpData]![Inst] & "'[/blue], FLO_Data.ChartNo =  [blue]'" & [tmpData]![ChartNo] & "'[/blue], FLO_Data.AcctNo = [blue]'" & [tmpData]![AcctNo] & "'[/blue], FLO_Data.Age = [blue]'" & [tmpData]![Age] & "'[/blue], FLO_Data.Sex = [blue]'" & [tmpData]![Sex] & "'[/blue], FLO_Data.Rescode = [blue]'" & [tmpData]![Rescode] & "'[/blue], FLO_Data.Postal = [blue]'" & [tmpData]![Postal] & "'[/blue], " & _

Skip single quote if that's a number, use # if it is a Date.

You can see your SQL easy if you do:

strSQL_Q2 = "UPDATE tmpData [...] SET ...

Debug.Print strSQL_Q2

Have fun.

---- Andy

 

[Andrzejek]

I would also aviod using in-line-IF statement:

[green]
    'If DCount("*", "tmpData") > 0 Then DoCmd.RunSQL strSQL_T[/green]
    If DCount("*", "qryUnmatched") > 0 Then DoCmd.RunSQL strSQL_Q1 Else
    DoCmd.RunSQL strSQL_Q2
    DoCmd.RunSQL strSQL_T[green]
    'DoCmd.SetWarnings True[/green]

It doesn't look like you have any [blue][tt]End If[/tt][/blue] anywhere....

Wouldn't be easier to do:

If DCount("*", "qryUnmatched") > 0 Then 
    DoCmd.RunSQL strSQL_Q1 
Else
    DoCmd.RunSQL strSQL_Q2
End If

DoCmd.RunSQL strSQL_T

Also, I would be very tempted to change names for your SQLs:
strSQL_T to [blue]strSQL_D[/blue] (D for DELETE)
strSQL_Q1 to [blue]strSQL_I[/blue] (I for INSERT)
strSQL_Q2 to [blue]strSQL_U[/blue] (U for UPDATE)
but that's just me....

Have fun.

---- Andy

 

[shelby55]

Hi Andy

Excellent feedback...I'll check this out right away and follow up with any further questions.

Very much appreciated!

 

[shelby55]

Hi Andy

Just a question: I copied and pasted the exact SQL text from the update query and that part is actually working...so not sure what about my current code you think is incorrect.

Also, does your comments address the issue of having a record missing upon import? Thanks.