Proper SQL Syntax for insert string

[GBalcom]

I've been out of VBA and access for months now, just hopping in to accomplish a quick little application, and I'm stuck....

I'm trying to use an ODBC adapter from Cdata to push information to a google calendar. I've got it hooked up, and I can read data within a DAO recordset, but .addnew isn't supported. So, I want to try to just insert with SQL. Please see my code below. I know the syntax is wrong on my strSQL, just can't figure out what it should be :banghead:

Public Sub sInsertEvent(strSummary As String, strDescription As String, StartDate As Date)


    '====================================================================
    ' Comments:  Inserts a new event into the google calendar
    ' State of Code: Under Development
    ' Params  :
    ' Returns :
    ' Created : 05/29/2015 12:55 PM GB
    ' Modified:
    '====================================================================
    
    'ErrorEnablerStart
    On Error GoTo PROC_ERR
    'ErrorEnablerEnd

Dim strSQL As String

Dim EndDate As Date

    EndDate = StartDate + 1
    

    strSQL = "INSERT INTO [GoogleApps_CalendarEvents] (CalendarId,Summary," & _
             "Description,AllDayEvent,StartDateTime,EndDateTime) VALUES (" & _
             "'schedule@atest.com',strSummary,strDescription,True,StartDate," & _
             "EndDate"
             
    Debug.Print strSQL
    
             
    CurrentDb.Execute strSQL, dbFailOnError
    
             
    
'====================================================================





    'ErrorHandlerStart
EXIT_PROCEDURE:
    Exit Sub

PROC_ERR:
    MsgBox Err.Description, vbCritical, "mGoogleApps.sInsertEvent"
    Resume EXIT_PROCEDURE
    'ErrorHandlerEnd

End Sub

 

[vbaInet]

If it were Access the correct syntax would be:

"INSERT INTO [GoogleApps_CalendarEvents] " & _
                       "(CalendarId, Summary, " & _
                       "Description, AllDayEvent, " & _
                       "StartDateTime,EndDateTime) " & _
           "VALUES ('schedule@atest.com', '" & strSummary & "', " & _
                   "'" & strDescription & "', True, " & _
                   "#" & StartDate & "#, #" & EndDate & "#;"

Your main problems would be inserting String and Date types because you would need to know the correct delimiters to insert those types into the Google Calendar.

You should first start with static data and first try inserting using numbers, then strings, then dates. Don't use variables just yet.

The other thing, is "schedule@atest.com" the right CalendarID?

 

[GBalcom]

Ok Great Thanks! I'll start with this on Monday and let you know how it goes!

 

[vbaInet]

Ok. I'll await your feedback.

 

[GBalcom]

Thank you VBAInet for your input. After a fresh start this morning, I tried to utilize a simple append query through the interface, and they appended fine.

I'm thinking of going in the following direction on this project instead.

Using VBA, build a temp table and populate it with the records I want to add.
Run the append query to push the records over to the google calendar

Create a separate temp table and populate it with records that need to be updated, then run an appropriate update query against this temp table.

More than likely this application will be updating and appending a dozen or so records at once, so this seems like a better way to go about it then sending over one record at a time with an sql statment.

More thoughts appreciated!

 

[vbaInet]

Very good

Yes it makes sense to batch update in this case and if one update fails, you can fix it and re-try for the next scheduled update.

The only thing you don't need is a temp table. You can utilise a query, filter it down to the relevant records, then push the filtered results to the Calendar.