AnsweredAssumed Answered

ADO / Excel and FileMaker ODBC

Question asked by simoncpage on Dec 13, 2018
Latest reply on Dec 13, 2018 by simoncpage

I have written a VBA script with ADO to create a new record in FileMaker via ODBC. I can pull data with no issue but I can't seem to write and I'm not sure if the issue is my script or the driver, can anyone help (this code below gives me a ODBC doesn't support the requested properties). If this is the driver is there another option to push data to FileMaker without the use of an export file. Thanks.

 

Sub Macro1()

    'Declare a Connection object

    Dim cnDB As New ADODB.Connection

    'Declare a Recordset Object

    Dim rsRecords As New ADODB.Recordset

    'Open the ODBC Connection using this statement

    'cnDB.Properties("Prompt") = adPromptAlways

    cnDB.Open "Portfolio ODBC", "*********", "**********"

    'rsRecords.Open "Select * from Excel_ODBC", cnDB, adOpenStatic

    rsRecords.Open "Select * from Excel_ODBC", cnDB, adOpenDynamic, adLockOptimistic, adCmdTable

    'Print the numberof records in A1 cell

    'Range("A1").Select

    'ActiveCell.FormulaR1C1 = rsRecords.RecordCount

    'Range("A2").Select

    fieldsArray = Array("ID", "SCHEME_NAME")

    ValuesArray = Array("123", "val2")

    rsRecords.AddNew fieldsArray, ValuesArray

    rsRecords.Update

    'Close everything and set the references to nothing

    rsRecords.Close

    Set rsRecords = Nothing

    cnDB.Close

    Set cnDB = Nothing

End Sub

Outcomes