Object does not support... when INSERTing into Access DB

[pitaszek]

Hi Guys,

I can't figure out what's wrong.

I want to insert only one value into one field but the number of fields is bigger (if it matters).

I reworked it from early binding to late binding.. maybe I am missing something or my query is super bad but it should not as similar one works on different db.

Generally it takes CleintFundName from the outside but I changed it to fixed value 'abcd'.

Private Const AccessConStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\networkdrive\myaccessdb.accdb;Persist Security Info=False; Jet OLEDB:Database Password=xxx;"
Sub ConnectToDB(ByVal ClientFundName, ByVal SQLStr)
   
    Dim Date1 As Date
    'MsgBox ClientFundName
    
    Dim TESTDB As Object
    Dim TESTDBCmd As Object
    
    Set TESTDB = CreateObject("ADODB.Connection")
    Set TESTDBCmd = CreateObject("ADODB.Recordset")
    
    TESTDB.ConnectionString = AccessConStr
    
    TESTDB.Open
    
    TESTDBCmd.ActiveConnection = TESTDB
    'GetInsertTexto ClientFundName, SQLStr
    'MsgBox SQLStr
    TESTDBCmd.CommandText = "INSERT INTO Tracker (Client_Fund_Name) VALUES ('abcd')"
    
    TESTDBCmd.Execute
    
    TESTDB.Close
    
    Set TESTDB = Nothing
    
End Sub
Sub GetInsertTexto(ByVal ClientFundName, ByRef SQLStr)
'MsgBox (Name1 & " " & Last_Name)
SQLStr = "INSERT INTO Tracker (Client_Fund_Name)" _
& "VALUES (" & "'" & ClientFundName & "'" & ")"
    
End Sub

Regards,
Witek

 

[wideboydixon]

I don't think you can set CommandText on a Recordset. Don't you want:

Set TESTDBCmd = CreateObject("ADODB.Command")

?

WBD

 

[RoryA]

A Recordset doesn't have a CommandText property or an .Execute method. I think you meant:

Set TESTDBCmd = CreateObject("ADODB.Command")

 

[pitaszek]

Perfect! Thank you guys.