CODEWITHSUNDEEP
ms-accessvba
user6788933
user6788933

Reputation: 287

Correct use of CurrentProject.Connection for ADODB.Connection?

In Access VBA, I needed to use parameters for database inserts/updates so I started using a ADODB command.

The database to insert is always the current one, so I use CurrentProject.Connection.

Everything works without using conn.Open if I try to open it it will return error 3705:Operation is not allowed when the object is open.

Am I missing something important that will hit a connections limit? Can someone suggest a better way?

Dim sql As String
sql = "INSERT INTO "
sql = sql + "[table]"
sql = sql + "(field1,field2)"
sql = sql + "VALUES (@field1,@field2)"

Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd
.ActiveConnection = conn
.CommandType = adCmdText
.CommandText = sql
.Parameters.Append .CreateParameter("@field1", adVarChar, adParamInput, 255, x)
.Parameters.Append .CreateParameter("@field2", adVarChar, adParamInput, 255, y)
.Execute
End With

Upvotes: 0

Views: 2794

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 48989

Are you open to using DAO? you can use this:

Dim q        As DAO.QueryDef
Dim strSQL   As String

strSQL = "INSERT INTO table1 " & _
         "(City, Province) " & _
         "Values([pCity], [pProvince])"

Set q = CurrentDb.CreateQueryDef("", strSQL)
q.Parameters("pCity") = "Edmonton"
q.Parameters("pProvince") = "Alberta"
q.Execute

Upvotes: 1

Related Questions