Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2012
    Posts
    32

    Unanswered: overwrite access.mdb from sql datatable

    I have a program that only talks to access. I am trying to synchronize the data through sql and then export to local access mdb files. Is there a way to overwrite an existing access database from a sql datatable that has the same schema?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use something like:
    Code:
    Sub GetFromSQL(ByVal Connection As String, ByVal LocalTableName As String, Optional ByVal SQLTableName As String)
    
        Dim qdf As DAO.QueryDef
        Dim strName As String
        Dim strSQL As String
        
        If Len(SQLTableName) = 0 Then SQLTableName = LocalTableName
        If DCount("*", "MSysObjects", "name='" & LocalTableName & "'") > 0 Then
            strSQL = "DROP TABLE " & LocalTableName
            CurrentDb.Execute strSQL, dbFailOnError
        End If
        strName = Format(Now, "yyyy-mm-dd_hh-nn-ss")
        Set qdf = CurrentDb.CreateQueryDef(strName)
        With qdf
            .Connect = Connection
            .SQL = "SELECT * FROM " & SQLTableName & ";"
        End With
        strSQL = "SELECT * INTO " & LocalTableName & " FROM [" & strName & "];"
        CurrentDb.Execute strSQL, dbFailOnError
        Set qdf = Nothing
        CurrentDb.QueryDefs.Delete strName
        
    End Sub
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •