• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 430
  • Last Modified:

need to refresh connection to sql after dropping table before importing

How do I refresh my connection before doing the file transfer?
I have an Access ADP application (back end sql server).  I'm building an import screen to import files.  The process is that the user selects an Access MDB file and supplies the table name to import.  Once the "import" button is clicked I drop the existing table using a stored procedure via docmd.runsql(exec stp_APDB_Drop_table import_TDA;) , I then perform the transfer using DoCmd.TransferDatabase acImport, "Microsoft Access", Me!WageReportPath, acTable, Me.tableName, "import_TDA", no

The issue is that the table gets imported as import_TDA1 because it doesn't recognze that the table (import_TDA) was dropped before performing the transfer.  How do I refresh my connection before doing the file transfer?
  • 5
  • 3
  • 2
  • +1
1 Solution
Couldn't you rename the table after import?
jvantassel1Author Commented:
I know I can't get points for answering my own question, but I used DoCmd.DeleteObject acTable, "import_TDA" which seems to work.  I'm not sure why this works and the drop table doesn't.  Any ideas?

I hope this helps someone. Feel free to close this question
jvantassel1Author Commented:
Once I bring the table in I then pull it into a working table to clean the data before appending it to the "real" table.
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

can you pop the code up?
I use the function below to refresh/switch my SQL connection between dev and prod environments.  You could modify it to only refresh the desired table or use it as is to refresh connections for linked tables.

I have a local table that stores both the dev and prod SQL connect strings.

call it this way
RefreshTableLinks("production", "refresh")

OM Gang
Public Function RefreshTableLinks(strEnvironment As String, strAddRefresh As String, _
        Optional strTableName As String) As String
'refreshes existing ODBC table links or adds new ODBC table link depending on value of strAddRefresh
'use strAddRefresh = refresh  or  strAddRefresh = add
'new ODBC linked table is added as name = strTableName with _ and first four characters of strEnvrionment appended
'returns "fail" on fail, returns name linked table (add) or name of last linked table (refresh) on success
On Error GoTo Err_RefreshTableLinks

    Dim tdf As DAO.TableDef
    Dim rs As DAO.Recordset
    Dim strLink As String, strDBName As String, strLocalTableName As String
    Dim strMsg As String, strProdLink As String, strDevLink As String
    Dim strSuffix As String, strResult As String
    Dim strLinkedTableName As String
        'initialize return variable
    strResult = "fail"
        'make sure we were passed expected values
    If strEnvironment <> "production" And strEnvironment <> "development" Then GoTo Exit_RefreshTableLinks
    If strAddRefresh <> "refresh" And strAddRefresh <> "add" Then GoTo Exit_RefreshTableLinks
        'name of local table with connection string info
    strLocalTableName = "SQL_ConnectionStrings"
        'name of SQL database we want to reconnect to
    strDBName = "The_MSSQL_db"
        'open recordset (as dynaset so we can use Find methods) on table and find record
        'for named database
    Set rs = CurrentDb.OpenRecordset(strLocalTableName, dbOpenDynaset)
    rs.FindFirst "SQLdbName = " & Chr(34) & strDBName & Chr(34)
    If rs.NoMatch Then
        strMsg = "No record for the database " & strDBName & " could be found in table " _
                & strTableName & "."
        MsgBox strMsg, , "Error Re-Linking SQL Tables"
        GoTo Exit_RefreshTableLinks
    End If
        'retrieve both production and development connection strings from table
    'strProdLink = rs!PRD_ConnectString
    'strDevLink = rs!DEV_ConnectString
    Select Case strEnvironment
        Case "production"
            strLink = rs!PRD_ConnectString
            'strLink = strProdLink
        Case "development"
            strLink = rs!DEV_ConnectString
            'strLink = strDevLink
        Case Else   'do nothing
    End Select
        'proceed based upon passed value of strAddRefresh
    Select Case strAddRefresh
        Case "refresh"
                'iterate through table definitions and refresh links for tables that have connection
            For Each tdf In CurrentDb.TableDefs
                    'get table name as return value
                strResult = tdf.Name
                    'don't refresh table names with a suffix of _test
                If Not strResult Like "*_test" Then
                    If Len(tdf.Connect) > 0 Then
                            'do not attempt to relink Outlook mapi tables
                        If Not tdf.Connect Like "Outlook*" Then
                            tdf.Connect = strLink
                        End If
                    End If
                End If
            Next tdf
        Case "add"
                'make sure we received a non-empty value for the option table name variable
            If strTableName = "" Then
                strMsg = "You must pass a valid table name for the table you want to link to."
                MsgBox strMsg, , "Function RefreshTableLinks"
                GoTo Exit_RefreshTableLinks
            End If
                'create suffix string we'll append to passed table name
            strSuffix = "_" & Left(strEnvironment, 4)
                'create name for linked table
            strLinkedTableName = "dbo_" & strTableName & strSuffix
                'link table using the specified connection string
            DoCmd.TransferDatabase acLink, "ODBC Database", strLink, acTable, strTableName, _
                'set return value to table name
            strResult = strLinkedTableName

        Case Else
            'do nothing
    End Select
        'clear object variables
    Set tdf = Nothing
    Set rs = Nothing
        'function return value
    RefreshTableLinks = strResult
    Exit Function
    MsgBox Err.Number & ", " & Err.Description, , "Error in function RefreshTableLinks"
    Resume Exit_RefreshTableLinks

End Function

Open in new window

jvantassel1Author Commented:
I see how this works for linked tables, how about a straight ADP app where the connection is persistent?  Here's all I did, quite simple.  I call this function from the on_click event of my button.  Then I use the transfer database along with some error checking

Function delImports()
On Error Resume Next
Dim strSQL As String
Dim myDelete1 As String
myDelete1 = "delete dbo.import_working_TDA;"

        DoCmd.SetWarnings False
        DoCmd.RunSQL (myDelete1)
        DoCmd.DeleteObject acTable, "import_TDA"

        DoCmd.SetWarnings True
End Function

DoCmd.TransferDatabase acImport, "Microsoft Access", Me!WageReportPath, acTable, Me.tableName, "import_TDA", no
I haven't ever worked with ADPs but you might try Application.Refreshdatabase after calling the SP to delete the table.
OM Gang
Currentdb.tabledefs.Refresh should refresh the tabledefs and Currentdb.tabledefs.delete("table") should remove the table links

Try working with those.
jvantassel1Author Commented:
I still have to test the refresh table defs, I've been caught up with things here at the office, so later today or tomorrow before I can get back to it.
Vadim RappCommented:
delete your table using Access own object model, then you won't have to refresh.

DoCmd.DeleteObject acTable, Tablename
jvantassel1Author Commented:
sorry it took so long to get back to this.  I tried using CurrentDb.TableDefs.Refresh to refresh my table connection but get the following error:

Object variable or With block variable not set

what do you suggest
Private Sub Command0_Click()
On Error GoTo ExitProc
Dim DivID As Integer
Dim TDA_TypeID As Integer
Dim mySQL As String
Dim mySQL2 As String
Dim mySQL0 As String
Dim strTDA_Name As String

'see if they selected a TDA
If ErrorCheck(2, 0) = True Then
    MsgBox "Correct Errors on Page"
    Exit Sub
'see if they really want to do the update
    If MsgBox("Continue with update?", vbQuestion + vbYesNo) = vbYes Then
        DoCmd.SetWarnings False
        DoCmd.Hourglass True
    'do the import
    'DivID = Me.cboTDA.Column(2)
    'TDA_TypeID = Me.cboTDA.Column(0)
    strTDA_Name = Me.TDA_Name
    mySQL0 = "exec stp_APDB_insert_TDA_Type_for_workingTDA '" & strTDA_Name & "', '" & myGetUserName & "'"
    mySQL = "exec stp_APDB_update_import_workingTDA_G_StaffID_DivID '" & myGetUserName & "'"
    mySQL2 = "exec stp_APDB_update_import_working_TDA_DivID_TDA_TypeID '" & strTDA_Name & "','" & myGetUserName & "'"
    DoCmd.RunSQL (mySQL0)
    DoCmd.RunSQL (mySQL)
    DoCmd.RunSQL (mySQL2)
    'clean up
        DoCmd.SetWarnings True
        DoCmd.Hourglass False
        lblComplete_DivisionTDA.Visible = True
    'they did not want to continue
        Exit Sub
    End If
End If

    Call ErrorCheck(2, Err)
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
   Exit Sub
   MsgBox "Error: " & Err.Number & ". " & Err.Description
   Resume ExitProc

End Sub

Open in new window

Vadim RappCommented:
> what do you suggest

throw it all away and replace with the one statement from http:#31046572

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now