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 · Accepted Answer · 2014-08-27 15:36:02Z

1

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.

answered Aug 27, 2014 at 15:36

Krish

5,9272 gold badges19 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Anpix Over a year ago

Thanks for the reply, but didn't solve my problem. The 3999 error still appears.

Krish Over a year ago

can you open your sharepoint list in design mode and make a screen? i would like to know the data types of each column you are trying to insert

Anpix Over a year ago

Sorry, but i don't know send images here. //> Title: Single line of Text // *Data: Date and Time // IdSistema: Lookup // IdOperação: Lookup // *Sonda: Lookup // *Status: Lookup // Resumo: Multiple lines of text // Situação: Multiple lines of text // Próximas: Multiple lines of text // Adicionais: Multiple lines of text // PróxPoço: Multiple lines of text // OK: Yes/No (Check box) // OldID: Number(1, 1.0, 100) // * = Required

Krish Over a year ago

oldid is required field not sure if your SharePoint workflow inserts auto number or id. I would suggest open the sharepoint list in access and try to add new record manually and see if [data, sondaID, 0] is sufficient to create a new record.

Anpix Over a year ago

I've tried to do this, and I could add new records without problems.

Uriel · Accepted Answer · 2016-02-20 08:04:45Z

0

Maybe this could help somebody. I did have the same problem and i solved it removing the sharepoint list from access and add it again. Take a seconds to make a sql query but it works.

greet

answered Feb 20, 2016 at 8:04

Uriel

1

Collectives™ on Stack Overflow

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

What i need:

What's happening?

My code now:

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

What i need:

What's happening?

My code now:

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related