How to use "INSERT INTO" command from Access VBA to Sharepoint

Question

I'm using a MS Access database linked with a Sharepoint Server. MS Access Forms as FrontEnd and Sharepoint Lists as BackEnd.

Today i can see all the informations from the lists using access forms, without problems.

What i need:

I'm trying to insert new registers on the list, using a SQL command: "INSERT INTO..."

if there is another possibility to insert the record in the list, may be useful

What's happening?

When i call the DoCmd.RunSQL(txtsql), i receive a runtime error 3999 saying i'm disconnected from the server.

My code now:

I tried using recordsets, but didn't succeed. I need to run this SQL many times, changing the string "txtSql" inside a loop. Like this:

Dim MaxSonda As Integer

'Get the max ID from the list
MaxSonda = Nz(DMax("IdSonda", "Sondas", "((Sondas.[Tipo Sonda])<>1 Or (Sondas.[Tipo Sonda]) Is Null)"), 0)
MsgBox "MaxSonda = " & MaxSonda

'Run the code for each "sonda"
Do While MaxSonda > 1
    If Nz(DLookup("[Tipo Sonda]", "Sondas", "Sondas!IdSonda = " & MaxSonda), 1) <> 1 Then
        DoCmd.OpenTable "Resumo", acViewNormal, acAdd
        DoCmd.GoToRecord acDataTable, "Resumo", acNewRec
        
        txtSql = "INSERT INTO Resumo ( Data, Sonda, Status ) SELECT #" & LastData + 1 & "#, " & MaxSonda & ", 0;"
        MsgBox txtSql
        DoCmd.RunSQL txtSql
        
        DoCmd.Close acTable, "Resumo", acSaveYes
    End If
    MaxSonda = MaxSonda - 1
Loop

P.S.: The MsgBox is just for check the steps

thanks for help

Krish Krish · Accepted Answer · 2014-08-27T15:36:02

You don't need to open the list/table to insert a record. I don't know why you are using loop to insert rows but if that is your intention try this SQL_COMMAND within your loop:

If Nz(DLookup("[Tipo Sonda]", "Sondas", "[IdSonda] = " & MaxSonda), 1) <> 1 Then
        txtSql = "INSERT INTO Resumo ( Data, Sonda, Status ) VALUES ('" & LastData + 1 & "'," & MaxSonda & ",0);"
        MsgBox txtSql
        DoCmd.RunSQL txtSql
End If

also note #tags are used to insert dates in Access, if you are intend to save dates save them in international format as strings like

vba.Format$([date_field],"yyyy-mm-dd hh:mm:ss")

this way you can just save as string without using the #tags.

How to use "INSERT INTO" command from Access VBA to Sharepoint

What i need:

What's happening?

My code now:

2 Answers