Generate INSERT INTO statements in MS-Access

Question

I like to export data (single records) from one Access database to another one in another country. The idea is that I want to send a text file with INSERT INTO statements per email and the receiving PC just executes these INSERT INTO statements. I wrote already the code to read and execute the INSERT INTO statements in these text files.

Obviously I have to generate the INSERT INTO statements.

Here is an example.

I have the following table:

Table1
Id                number
PersonName        text
DoB               date, can be empty
NumberOfChildern  number, can be empty

I select the data like this:

SELECT Id, PersonName, DoB, NumberOfChildern FROM Table1;

What I want to generate are statements like this:

INSERT INTO Table1 (Id, PersonName, DoB, NumberOfChildern ) VALUES (1, ‘Peter’, #5-17-1990#, 1)

If all fields are always filled in then I could write one time the code and that's it. But there is a problem if a couple of fields might contain data or maybe no data.

Here are some similar but different versions of the above statement:

INSERT INTO Table1 (Id, PersonName, DoB, NumberOfChildern ) VALUES (1, ‘Peter’, #5-17-1990#, 1)
INSERT INTO Table1 (Id, PersonName, NumberOfChildern ) VALUES (1, ‘Peter’, 1)
INSERT INTO Table1 (Id, PersonName, DoB ) VALUES (1, ‘Peter’, #5-17-1990#)
INSERT INTO Table1 (Id, PersonName ) VALUES (1, ‘Peter’)

With just two fields which can contain NULL values there are already 4 different versions of this statement and with more fields it becomes more and more complicated (not really complicated but more work).

I think about writing code in VBA which analyzes the table and the records which I want to export to check which kind of fields are used (i.e. date) and then generate statements like above. I am sure I can do this but I wonder if maybe others did this before. I don't want to reinvent the wheel. But searching for "generate SQL insert statements" is not really efficient.

Any ideas?

Answer 1

It's your lucky day. I have done this for SQL Server - with a few modifications done below it should work for Access SQL.

The key is to insert VALUES NULL, not create different statements if values are null.

The SET IDENTITY_INSERT ON/OFF probably isn't needed for Access.

Gustav has posted a generic function that can replace all Sqlify/SqlDate etc. helper functions and covers more data types.

Public Sub InsertStatementsSql(ByVal sTABLE As String)

    Dim DB As DAO.Database
    Dim TD As DAO.TableDef
    Dim RS As DAO.Recordset
    Dim fld As DAO.Field
    Dim sKpl As String
    Dim sStart As String
    Dim sValues As String
    Dim S As String
    Dim v As Variant
    Dim i As Long
    Dim bIdentity As Boolean

    Set DB = CurrentDb
    Set TD = DB.TableDefs(sTABLE)
    Set RS = DB.OpenRecordset(sTABLE, dbOpenSnapshot)


    ' Check for Autonumber/IDENTITY column
    bIdentity = False
    For i = 0 To TD.Fields.count - 1
        If (TD.Fields(i).Attributes And dbAutoIncrField) > 0 Then
            bIdentity = True
            Exit For
        End If
    Next i

    If bIdentity Then
        sKpl = sKpl & "SET IDENTITY_INSERT " & sTABLE & " ON;" & vbCrLf & vbCrLf
    End If

    ' "INSERT INTO ... VALUES " for every line
    For i = 0 To TD.Fields.count - 1
        sStart = StrAppend(sStart, TD.Fields(i).Name, ", ")
    Next i
    sStart = "INSERT INTO " & sTABLE & " (" & sStart & ") VALUES "

    ' One line per record
    Do While Not RS.EOF
        sValues = ""
        For i = 0 To TD.Fields.count - 1
            v = RS(i)
            If IsNull(v) Then
                S = "NULL"
            Else
                Set fld = TD.Fields(i)
                Select Case fld.Type
                    Case dbText, dbMemo: S = Sqlify(CStr(v))
                    Case dbDate: S = SqlDate(CDate(v))
                    Case dbDouble, dbSingle: S = SqlNumber(CDbl(v))
                    Case Else: S = CStr(v)
                End Select
            End If
            sValues = StrAppend(sValues, S, ", ")
        Next i
        ' Append line to full SQL
        sKpl = sKpl & vbCrLf & sStart & " (" & sValues & ");"
        RS.MoveNext
    Loop
    RS.Close
    Set TD = Nothing

    If bIdentity Then
        sKpl = sKpl & vbCrLf & vbCrLf & "SET IDENTITY_INSERT " & sTABLE & " OFF;" & vbCrLf
    End If

    Debug.Print sKpl

    ' see https://support.microsoft.com/en-us/kb/210216 or https://msdn.microsoft.com/en-us/library/office/ff192913.aspx
    ' or https://stackoverflow.com/a/25431633/3820271
    'ClipBoard_SetData sKpl

End Sub

' ------------------- helper functions -----------------

'  ein'string --> 'ein''string'
Public Function Sqlify(ByVal S As String) As String

    S = Replace(S, "'", "''")
    S = "'" & S & "'"
    Sqlify = S

End Function

Public Function SqlDate(vDate As Date) As String
    SqlDate = "#" & Format(vDate, "yyyy-mm-dd") & "#"
End Function

Public Function SqlNumber(num As Double) As String
    SqlNumber = Replace(CStr(num), ",", ".")
End Function

Public Function StrAppend(sBase As String, sAppend As Variant, sSeparator As String) As String

    If Len(sAppend) > 0 Then
        If sBase = "" Then
            StrAppend = Nz(sAppend, "")
        Else
            StrAppend = sBase & sSeparator & Nz(sAppend, "")
        End If
    Else
        StrAppend = sBase
    End If

End Function