Export Function Not Working If Link to Diff Copy of Tables

Sinfathisar · Aug 21, 2014

I am having an odd problem. I have a code module in my interface with 3 functions that run via separate forms with user inputs. Each function is similar in design, exporting data from a generated query to excel for input into external software.

The database is split into an interface FE (Access 2007) with linked tables BE (Access 2000). I have successfully tested each of the 3 functions in my code module with a set of tables containing fake test data. However, I have discovered that if I link to a different copy of the .mdb tables, 2 of the export functions no longer work and the third one still works fine.

I am stumped as to what is causing the problem but the part that is not working correctly is the loop.

Here is my code module:

Code:

 Option Compare Database
Option Explicit
 Public Function CreateQCStaticChartsforReports() As Boolean
 'Define variables for Static Chart creation
Dim qdf As DAO.QueryDef
Dim strSQLStatic As String
Dim BookName As String
Dim SettingsBookName As String
 'Set values for settings export
'This one does the channel
If Forms!Chart_Export_Static!Combo1 = Forms!Chart_Export_Static!Combo1.ItemData(0) Then
        Forms!Chart_Export_Static!channel = "Channel 1"
    ElseIf Forms!Chart_Export_Static!Combo1 = Forms!Chart_Export_Static!Combo1.ItemData(1) Then
        Forms!Chart_Export_Static!channel = "Channel 2"
    ElseIf Forms!Chart_Export_Static!Combo1 = Forms!Chart_Export_Static!Combo1.ItemData(2) Then
        Forms!Chart_Export_Static!channel = "Channel 3"
    ElseIf Forms!Chart_Export_Static!Combo1 = Forms!Chart_Export_Static!Combo1.ItemData(3) Then
        Forms!Chart_Export_Static!channel = "Channel 4"
    ElseIf Forms!Chart_Export_Static!Combo1 = Forms!Chart_Export_Static!Combo1.ItemData(4) Then
        Forms!Chart_Export_Static!channel = "All"
End If
 'This one does the static response tolerance
Forms!Chart_Export_Static!statictol = Forms!Chart_Export_Static!SRespTol
 'This one does the static curve response tolerance
'If Forms!Chart_Export_Static!Combo4 = Forms!Chart_Export_Static!Combo4.ItemData(0) Then
'        Forms!Chart_Export_Static!staticcurvetol = "0.1"
'    ElseIf Forms!Chart_Export_Static!Combo4 = Forms!Chart_Export_Static!Combo4.ItemData(1) Then
'        Forms!Chart_Export_Static!staticcurvetol = "0.2"
'End If
 'For exporting the Static Chart settings from the form controls to excel
Dim xlsApp As Excel.Application
Set xlsApp = CreateObject("Excel.Application")
SettingsBookName = DLookup("[projectpath]", "[Project_Defaults]") & "\Database\Grapher\" & "Tolerance_Settings.xls"
With xlsApp
    .Visible = False
    .Workbooks.Open (SettingsBookName)
    .Sheets("StaticSettings").Select
    .Range("A1") = "Channels"
    .Range("A2") = Forms!Chart_Export_Static!channel.Value
    .Range("B1") = "Static"
    .Range("B2") = ((Forms!Chart_Export_Static!statictol.Value) / 100)
'    .Range("C1") = "Static Curve"
'    .Range("C2") = Forms!Chart_Export_Static!staticcurvetol.Value
    .ActiveWorkbook.Close True
    
End With
Set xlsApp = Nothing
 'Define variables for creating loop to export all teams
Dim intCounter As Integer
Dim cboCode As ComboBox
 
' Set the variable to point to combobox that holds Team ID.
    Set cboCode = Forms!Chart_Export_Static!cboTeam
    
 ' Loop through all items (Team IDs) to create exported excel file for each team.
    For intCounter = 0 To cboCode.ListCount - 1
    
        'sql for static test charts export
        strSQLStatic = "SELECT Static_Repeatability_Test_Table.Static_Repeatability_ID, Static_Repeatability_Test_Table.Collection_Date, Static_Repeatability_Test_Table.Team_ID, Static_Repeatability_Test_Table.Static_Test_Item, Static_Repeatability_Test_Table.Static_Response_CH1, Static_Repeatability_Test_Table.Static_Response_CH2, Static_Repeatability_Test_Table.Static_Response_CH3, Static_Repeatability_Test_Table.Static_Response_CH4, Seed_Test_Item_Table.Response_Value_CH1, Seed_Test_Item_Table.Response_Value_CH2, Seed_Test_Item_Table.Response_Value_CH3, Seed_Test_Item_Table.Response_Value_CH4, Seed_Test_Item_Table.Static_Test_Item_Height, Format(Static_Repeatability_Test_Table.Collection_Date, 'mdyyyy') AS Filename " & _
        "FROM Static_Repeatability_Test_Table INNER JOIN Seed_Test_Item_Table ON Static_Repeatability_Test_Table.[Static_Test_Item] = Seed_Test_Item_Table.[Test_Item_ID] " & _
        "WHERE (((Static_Repeatability_Test_Table.Collection_Date) Between Int([Forms]![Chart_Export_Static]![StartDate]) And (Int([Forms]![Chart_Export_Static]![EndDate])+0.99))) AND  (((Static_Repeatability_Test_Table.Team_ID)='" & cboCode.ItemData(intCounter) & "')) " & _
        "ORDER BY Static_Repeatability_Test_Table.Collection_Date DESC, Static_Repeatability_Test_Table.Team_ID, Static_Repeatability_Test_Table.Static_Test_Item;"
         'Create the query using SQL defined above
        Set qdf = CurrentDb.CreateQueryDef("Static", strSQLStatic)
         'Export the query using SQL defined above, one file for each team, with naming convention and filepath to appropriate subfolder for Grapher based on chart type selection
        If Forms!Chart_Export_Static!Combo2 = Forms!Chart_Export_Static!Combo2.ItemData(0) Then
                'Get the workbook name
                BookName = DLookup("[projectpath]", "[Project_Defaults]") & "\Database\Grapher\Static\" & cboCode.ItemData(intCounter) & "_Static.xls"
                'Export querydef into specified .xls file
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Static", BookName, True
            ElseIf Forms!Chart_Export_Static!Combo2 = Forms!Chart_Export_Static!Combo2.ItemData(1) Then
                'Get the workbook name
                BookName = DLookup("[projectpath]", "[Project_Defaults]") & "\Database\Grapher\StaticCurve\" & cboCode.ItemData(intCounter) & "_StaticCurve.xls"
                'Export querydef into specified .xls file
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Static", BookName, True
        End If
        
        'Fill the Static Chart Table with a record for each chart file that is exported for linking chart image to report
        
        'Creates the date part of the filename
        Forms!Chart_Export_Static!dateforfilename = Replace(Forms!Chart_Export_Static!EndDate, "/", "")
    
        'Define variables for Insert statements (one for "Static" charts, and one for "StaticCurve" charts)
        Dim Insert_StaticChartTable As String
        Dim Insert_StaticChartTable2 As String
        
        'Set the SQL statement for "Static" charts
        Insert_StaticChartTable = "INSERT INTO Static_Chart_Table ([Team_ID], [Static_Chart_Date], [Static_Chart_Type], [Static_Chart_Object], [TimeStamp], [Static_Chart_ID]) " & _
        "VALUES ('" & cboCode.ItemData(intCounter) & "', Format([Forms]![Chart_Export_Static]![EndDate], 'm/d/yyyy'), 'Static', '\Database\Grapher\Static\' & Forms!Chart_Export_Static![dateforfilename] & '_' & '" & cboCode.ItemData(intCounter) & "' & '_Static.png', Now(), Forms!Chart_Export_Static![dateforfilename] & '_' & '" & cboCode.ItemData(intCounter) & "' & '_Static.png');"
        'Set the SQL statement for "StaticCurve" charts
        Insert_StaticChartTable2 = "INSERT INTO Static_Chart_Table ([Team_ID], [Static_Chart_Date], [Static_Chart_Type], [Static_Chart_Object], [TimeStamp], [Static_Chart_ID]) " & _
        "VALUES ('" & cboCode.ItemData(intCounter) & "', Format([Forms]![Chart_Export_Static]![EndDate], 'm/d/yyyy'), 'StaticCurve', '\Database\Grapher\StaticCurve\' & Forms!Chart_Export_Static![dateforfilename] & '_' & '" & cboCode.ItemData(intCounter) & "' & '_StaticCurve.png', Now(), Forms!Chart_Export_Static![dateforfilename] & '_' & '" & cboCode.ItemData(intCounter) & "' & '_StaticCurve.png');"
        
        
        'First turns warnings off
        DoCmd.SetWarnings False
        
        'Executes the appropriate action query SQL statement(s) defined above to add records with chart information for linking images to reports
        If DCount("*", "Static") > 0 Then
            If Forms!Chart_Export_Static!Combo2 = Forms!Chart_Export_Static!Combo2.ItemData(0) Then
                    DoCmd.RunSQL Insert_StaticChartTable
                ElseIf Forms!Chart_Export_Static!Combo2 = Forms!Chart_Export_Static!Combo2.ItemData(1) Then
                    DoCmd.RunSQL Insert_StaticChartTable2
                ElseIf Forms!Chart_Export_Static!Combo2 = Forms!Chart_Export_Static!Combo2.ItemData(2) Then
                    DoCmd.RunSQL Insert_StaticChartTable
                    DoCmd.RunSQL Insert_StaticChartTable2
            End If
        End If
        
        'Turns warnings back on
        DoCmd.SetWarnings True
        
        'Clean up the query that was created above
        qdf.Close
        Set qdf = Nothing
        DoCmd.DeleteObject acQuery, "Static"
        
Next
 'msg box for when completed
MsgBox ("Export completed and database updated!!" & vbNewLine & "Remember to run the Grapher script and generate image files!")
 
End Function
 Public Function CreateQCIVSChartsforReports() As Boolean
 'Define variables for IVS Chart creation
Dim qdf As DAO.QueryDef
Dim strSQLIVS As String
Dim BookName As String
Dim SettingsBookName As String
 'Set values for settings export
'This one does the channel
If Forms!Chart_Export_IVS!Combo1 = Forms!Chart_Export_IVS!Combo1.ItemData(0) Then
        Forms!Chart_Export_IVS!channel = "Channel 1"
    ElseIf Forms!Chart_Export_IVS!Combo1 = Forms!Chart_Export_IVS!Combo1.ItemData(1) Then
        Forms!Chart_Export_IVS!channel = "Channel 2"
    ElseIf Forms!Chart_Export_IVS!Combo1 = Forms!Chart_Export_IVS!Combo1.ItemData(2) Then
        Forms!Chart_Export_IVS!channel = "Channel 3"
    ElseIf Forms!Chart_Export_IVS!Combo1 = Forms!Chart_Export_IVS!Combo1.ItemData(3) Then
        Forms!Chart_Export_IVS!channel = "Channel 4"
    ElseIf Forms!Chart_Export_IVS!Combo1 = Forms!Chart_Export_IVS!Combo1.ItemData(4) Then
        Forms!Chart_Export_IVS!channel = "All"
End If
 'This one does the ivs response tolerance
Forms!Chart_Export_IVS!ivsresptol = Forms!Chart_Export_IVS!IRespTol / 100
 'This one does the ivs response tolerance criteria
If Forms!Chart_Export_IVS!Combo2 = Forms!Chart_Export_IVS!Combo2.ItemData(0) Then
        Forms!Chart_Export_IVS!ivsresptolcrit = "+/-"
    ElseIf Forms!Chart_Export_IVS!Combo2 = Forms!Chart_Export_IVS!Combo2.ItemData(1) Then
        Forms!Chart_Export_IVS!ivsresptolcrit = ">/="
End If
 'This one does the ivs position tolerance
Forms!Chart_Export_IVS!ivspostol = Forms!Chart_Export_IVS!IPosTol
 'This one does the ivs position units
Forms!Chart_Export_IVS!ivsposunit = Forms!Chart_Export_IVS!IPosUnits
 'For exporting the IVS Chart settings from the form controls to excel
Dim xlsApp As Excel.Application
Set xlsApp = CreateObject("Excel.Application")
SettingsBookName = DLookup("[projectpath]", "[Project_Defaults]") & "\Database\Grapher\" & "Tolerance_Settings.xls"
With xlsApp
    .Visible = False
    .Workbooks.Open (SettingsBookName)
    .Sheets("IVSSettings").Select
    .Range("A1") = "Channels"
    .Range("A2") = Forms!Chart_Export_IVS!channel.Value
    .Range("B1") = "IVS Response"
    .Range("B2") = Forms!Chart_Export_IVS!ivsresptol.Value
    .Range("C1") = "IVS Postion"
    .Range("C2") = Forms!Chart_Export_IVS!ivspostol.Value
    .Range("D1") = "Units"
    .Range("D2") = Forms!Chart_Export_IVS!ivsposunit.Value
    .Range("E1") = "Response Tol Criteria"
    .Range("E2") = Forms!Chart_Export_IVS!ivsresptolcrit.Value
    .ActiveWorkbook.Close True
    
End With
Set xlsApp = Nothing
 'Define variables for creating loop to export all teams and items
Dim intCounter As Integer
Dim cboCode As ComboBox
Dim intCounter2 As Integer
Dim cboCode2 As ComboBox
 ' Set the variable to point to combobox that holds Team ID.
    Set cboCode = Forms!Chart_Export_IVS!cboTeam
    
 ' Loop through all items (Team IDs) to create exported excel file for each team.
    For intCounter = 0 To cboCode.ListCount - 1
    
        ' Set the variable to point to combobox that holds Item ID.
        Set cboCode2 = Forms!Chart_Export_IVS!cboItem
    
        ' Loop through all items (Item IDs) to create exported excel file for each team.
        For intCounter2 = 0 To cboCode2.ListCount - 1
            
            'sql for ivs test charts export
            strSQLIVS = "SELECT IVS_Daily_Results_Table.[IVS_Test_ID], IVS_Daily_Results_Table.[Date], IVS_Daily_Results_Table.[Team_ID], IVS_Daily_Results_Table.[Geophysical_Sensor], OnlyDigits(IVS_Daily_Results_Table.[Test_Item_ID]) AS Test_Item_ID, IVS_Daily_Results_Table.[IVS_Response_Ch1], IVS_Daily_Results_Table.[IVS_Response_Ch2], IVS_Daily_Results_Table.[IVS_Response_Ch3], IVS_Daily_Results_Table.[IVS_Response_Ch4], Seed_Test_Item_Table.[Response_Value_CH1], Seed_Test_Item_Table.[Response_Value_CH2], Seed_Test_Item_Table.[Response_Value_CH3], Seed_Test_Item_Table.[Response_Value_CH4], IVS_Daily_Results_Table.[X_Offset], IVS_Daily_Results_Table.[Y_Offset], IVS_Daily_Results_Table.[IVS_X], IVS_Daily_Results_Table.[IVS_Y], Format(IVS_Daily_Results_Table.[Date], 'mdyyyy') AS Filename " & _
            "FROM IVS_Daily_Results_Table INNER JOIN Seed_Test_Item_Table ON IVS_Daily_Results_Table.Test_Item_ID = Seed_Test_Item_Table.Test_Item_ID " & _
            "WHERE (((IVS_Daily_Results_Table.[Date]) Between Int([Forms]![Chart_Export_IVS]![StartDate]) And (Int([Forms]![Chart_Export_IVS]![EndDate])+0.99))) AND  (((IVS_Daily_Results_Table.[Team_ID])='" & cboCode.ItemData(intCounter) & "')) AND (((IVS_Daily_Results_Table.[Test_Item_ID])='" & cboCode2.ItemData(intCounter2) & "'))" & _
            "ORDER BY IVS_Daily_Results_Table.[Date] DESC , IVS_Daily_Results_Table.[Team_ID], IVS_Daily_Results_Table.[Test_Item_ID];"
            
            'Create the query using SQL defined above
            Set qdf = CurrentDb.CreateQueryDef(cboCode2.ItemData(intCounter2), strSQLIVS)
 
            'export ivs response files
            'Get the workbook name
            BookName = DLookup("[projectpath]", "[Project_Defaults]") & "\Database\Grapher\IVS\" & cboCode.ItemData(intCounter) & "_IVS.xls"
            'Export querydef into specified .xls file
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, cboCode2.ItemData(intCounter2), BookName, True
            
            'Fill the IVS Chart Table with a record for each chart file that is exported for linking chart image to report
            
            'Creates the date part of the filename
            Forms!Chart_Export_IVS!dateforfilename = Replace(Forms!Chart_Export_IVS!EndDate, "/", "")
            
            'Define variable for Insert statement (IVS Response Chart)
            Dim Insert_IVSChartTable As String
            
            Insert_IVSChartTable = "INSERT INTO IVS_Chart_Table ([Team_ID], [IVS_Item_ID], [IVS_Chart_Date], [IVS_Chart_Type], [IVS_Chart_Object], [TimeStamp], [IVS_Chart_ID]) " & _
            "VALUES ('" & cboCode.ItemData(intCounter) & "', '" & cboCode2.ItemData(intCounter2) & "', Format([Forms]![Chart_Export_IVS]![EndDate], 'm/d/yyyy'), 'IVSResponsePosition', '\Database\Grapher\IVS\' & Forms!Chart_Export_IVS![dateforfilename] & '_' & '" & cboCode.ItemData(intCounter) & "' & '_IVS_' & '" & cboCode2.ItemData(intCounter2) & "' & '.png', Now(), Forms!Chart_Export_IVS![dateforfilename] & '_' & '" & cboCode.ItemData(intCounter) & "' & '_IVS_' & '" & cboCode2.ItemData(intCounter2) & "' & '.png');"
            
            If DCount("*", cboCode2.ItemData(intCounter2)) > 0 Then
            'First turns warnings off
            DoCmd.SetWarnings False
            'Executes the action query SQL statements defined above to add records with chart information for linking images to reports
            DoCmd.RunSQL Insert_IVSChartTable
            'Turns warnings back on
            DoCmd.SetWarnings True
            End If
            
            'Clean up the query that was created above
            qdf.Close
            Set qdf = Nothing
            DoCmd.DeleteObject acQuery, cboCode2.ItemData(intCounter2)
    Next
    
Next
 'msg box for when completed
MsgBox ("Export completed and database updated!!" & vbNewLine & "Remember to run the Grapher script and generate image files!")
 End Function
 Public Function CreateQCGPSChartsforReports() As Boolean
 'Define variables for IVS Chart creation
Dim qdf As DAO.QueryDef
Dim strSQLGPS As String
Dim BookName As String
Dim SettingsBookName As String
 'Set values for settings export
'This one does the static position tolerance
Forms!Chart_Export_GPSCheck!gpspostol = Forms!Chart_Export_GPSCheck!GPosTol
 'This one does the static position units
Forms!Chart_Export_GPSCheck!gpsposunit = Forms!Chart_Export_GPSCheck!GPosUnits
 'For exporting the IVS Chart settings from the form controls to excel
Dim xlsApp As Excel.Application
Set xlsApp = CreateObject("Excel.Application")
SettingsBookName = DLookup("[projectpath]", "[Project_Defaults]") & "\Database\Grapher\" & "Tolerance_Settings.xls"
With xlsApp
    .Visible = False
    .Workbooks.Open (SettingsBookName)
    .Sheets("GPSSettings").Select
    .Range("A1") = "GPS Position"
    .Range("A2") = Forms!Chart_Export_GPSCheck!gpspostol.Value
    .Range("B1") = "Units"
    .Range("B2") = Forms!Chart_Export_GPSCheck!gpsposunit.Value
    .ActiveWorkbook.Close True
    
End With
Set xlsApp = Nothing
 'Define variables for creating loop to export all teams
Dim intCounter As Integer
Dim cboCode As ComboBox
 ' Set the variable to point to combobox that holds Team ID.
    Set cboCode = Forms!Chart_Export_GPSCheck!cboTeam
  ' Loop through all items (Team IDs) to create exported excel file for each team.
    For intCounter = 0 To cboCode.ListCount - 1
    
        'sql for GPS Check charts export
        strSQLGPS = "SELECT Geodetic_Functionality_Table.Geodetic_Functionality_ID, Geodetic_Functionality_Table.Date, Geodetic_Functionality_Table.Team_ID, Geodetic_Functionality_Table.Offset_X, Geodetic_Functionality_Table.Offset_Y, OnlyDigits([Geodetic_Functionality_Table].[Date]) AS Filename " & _
        "FROM Geodetic_Functionality_Table " & _
        "WHERE (((Geodetic_Functionality_Table.[Date]) Between Int([Forms]![Chart_Export_GPSCheck]![StartDate]) And (Int([Forms]![Chart_Export_GPSCheck]![EndDate])+0.99))) AND  (((Geodetic_Functionality_Table.[Team_ID])='" & cboCode.ItemData(intCounter) & "')) " & _
        "ORDER BY Geodetic_Functionality_Table.Date DESC;"
         'Create the query using SQL defined above
        Set qdf = CurrentDb.CreateQueryDef("GPS", strSQLGPS)
         'export gps check files
            'Get the workbook name
            BookName = DLookup("[projectpath]", "[Project_Defaults]") & "\Database\Grapher\GPS\" & cboCode.ItemData(intCounter) & "_GPS.xls"
            'Export querydef into specified .xls file
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "GPS", BookName, True
            
            'Fill the GPS Chart Table with a record for each chart file that is exported for linking chart image to report
            
            'Creates the date part of the filename
            Forms!Chart_Export_GPSCheck!dateforfilename = Replace(Forms!Chart_Export_GPSCheck!EndDate, "/", "")
            
            'Define variable for Insert statement (GPS Response Chart)
            Dim Insert_GPSChartTable As String
            
            Insert_GPSChartTable = "UPDATE Geodetic_Functionality_Table " & _
            "SET Geodetic_Functionality_Table.[Offset_Image] = '\Database\Grapher\GPS\' & Forms!Chart_Export_GPSCheck![dateforfilename] & '_' & '" & cboCode.ItemData(intCounter) & "' & '_GPS.png', Geodetic_Functionality_Table.[TimeStamp] = Now() " & _
            "WHERE (((Geodetic_Functionality_Table.[Date]) = Forms!Chart_Export_GPSCheck![EndDate]) AND ((Geodetic_Functionality_Table.[Team_ID]) = '" & cboCode.ItemData(intCounter) & "'));"
            
            If DCount("*", "GPS") > 0 Then
            'First turns warnings off
            DoCmd.SetWarnings False
            'Executes the action query SQL statements defined above to add records with chart information for linking images to reports
            DoCmd.RunSQL Insert_GPSChartTable
            'Turns warnings back on
            DoCmd.SetWarnings True
            End If
            
            'Clean up the query that was created above
            qdf.Close
            Set qdf = Nothing
            DoCmd.DeleteObject acQuery, "GPS"
            
Next
 'msg box for when completed
MsgBox ("Export completed and database updated!!" & vbNewLine & "Remember to run the Grapher script and generate image files!")
 End Function

Rx_ · Aug 21, 2014

My suggestion would be to write an error trap.
On Error -
For each error - make a Debug.Print including err.number err.description.
then resume next

Some things to look for since it is a different DB being connected.
Network (or other) delay times.
In the code module - Tools Reference - make a note of the versions being used
Start there and report back.

Sinfathisar · Aug 22, 2014

Thanks for the reminder to error trap. I had to look it up again to get the trapping right - I had tried some earlier today and I didn't set it up right.

Looks like I got my answer. Someone entered fake data in the other .mdb into the wrong field (still waiting on an answer from the tester to see how that actually occurred so I can prevent it from happening again). That error in data entry left Forms!Chart_Export_Static!cboTeam null and the loop couldn't function with a null value...

Export Function Not Working If Link to Diff Copy of Tables

Sinfathisar

Registered User.

Rx_

Nothing In Moderation

Sinfathisar

Registered User.

Similar threads

Users who are viewing this thread