running an access append query with ado from VB?

[Christina Haller]

Hi
How can I get this to work. I want to call an append query inside of an
msaccess database from my vb 6 application.

Is that possible?

thanks
Christina

 

[Brendan Reynolds]

This is the query I used for testing ...

PARAMETERS [NewName] Text ( 255 ), [NewDescription] Text ( 255 );
INSERT INTO Categories ( CategoryName, Description )
VALUES( [NewName] , [NewDescription] );

And here's the code ...

Public Sub TestAppend()

Dim cnn As ADODB.Connection
Dim cmm As ADODB.Command
Dim prm As ADODB.Parameter
Dim lngRecords As Long

Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\DSDATA\Northwind.mdb;Persist Security Info=False"
cnn.Open
Set cmm = New ADODB.Command
With cmm
.ActiveConnection = cnn
.CommandText = "qryTest"
.CommandType = adCmdStoredProc
Set prm = .CreateParameter("[NewName]", adVarChar, adParamInput,
255, "My New Category")
.Parameters.Append prm
Set prm = .CreateParameter("[NewDescription]", adVarChar,
adParamInput, 255, "A description of my new category")
.Parameters.Append prm
.Execute lngRecords
End With
cnn.Close

MsgBox lngRecords & " appended"

End Sub

 

[Christina Haller]

Thank you so much Brendan.

It works perfectly!

Greez
Christina

This is the query I used for testing ...

PARAMETERS [NewName] Text ( 255 ), [NewDescription] Text ( 255 );
INSERT INTO Categories ( CategoryName, Description )
VALUES( [NewName] , [NewDescription] );

And here's the code ...

Public Sub TestAppend()

Dim cnn As ADODB.Connection
Dim cmm As ADODB.Command
Dim prm As ADODB.Parameter
Dim lngRecords As Long

Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\DSDATA\Northwind.mdb;Persist Security Info=False"
cnn.Open
Set cmm = New ADODB.Command
With cmm
.ActiveConnection = cnn
.CommandText = "qryTest"
.CommandType = adCmdStoredProc
Set prm = .CreateParameter("[NewName]", adVarChar, adParamInput,
255, "My New Category")
.Parameters.Append prm
Set prm = .CreateParameter("[NewDescription]", adVarChar,
adParamInput, 255, "A description of my new category")
.Parameters.Append prm
.Execute lngRecords
End With
cnn.Close

MsgBox lngRecords & " appended"

End Sub

--
Brendan Reynolds (MVP)

Hi
How can I get this to work. I want to call an append query inside of an
msaccess database from my vb 6 application.

Is that possible?

thanks
Christina

 

[onedaywhen]

This is the query I used for testing ...

PARAMETERS [NewName] Text ( 255 ), [NewDescription] Text ( 255 );
INSERT INTO Categories ( CategoryName, Description )
VALUES( [NewName] , [NewDescription] );

And here's the code ...

Public Sub TestAppend()

Dim cnn As ADODB.Connection
Dim cmm As ADODB.Command
Dim prm As ADODB.Parameter
Dim lngRecords As Long

Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\DSDATA\Northwind.mdb;Persist Security Info=False"
cnn.Open
Set cmm = New ADODB.Command
With cmm
.ActiveConnection = cnn
.CommandText = "qryTest"
.CommandType = adCmdStoredProc
Set prm = .CreateParameter("[NewName]", adVarChar, adParamInput,
255, "My New Category")
.Parameters.Append prm
Set prm = .CreateParameter("[NewDescription]", adVarChar,
adParamInput, 255, "A description of my new category")
.Parameters.Append prm
.Execute lngRecords
End With
cnn.Close

MsgBox lngRecords & " appended"

End Sub

Brendan, Here's a tip for you: you can get ADO to create the Command
object, complete with parameters, data types, default values etc e.g.

Sub tester()

Dim Cat As Object
Set Cat = CreateObject("ADOX.Catalog")
With Cat
.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data source=C:\DSDATA\Northwind.mdb"

Dim Cmd As Object
Set Cmd = .Procedures("qryTest").Command
End With

With Cmd
.Parameters("NewName").Value = _
"My New Category"
.Parameters("NewDescription").Value = _
"A description of my new category"

Dim lngRecords As Long
.Execute lngRecords
End With

MsgBox lngRecords & " appended"
End Sub


I don't see any advantage in creating the Command from scratch. It
would be more efficient, in terms of coding and performance, to simply
parse the values out in SQL code e.g.

Sub tester2()

Dim Con As Object
Set Con = CreateObject("ADODB.Connection")
With Con
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data source=C:\DSDATA\Northwind.mdb"
.Open

Dim lngRecords As Long
.Execute _
"EXEC qryTest" & _
" 'My New Cat2'," & _
" 'A description of cat2';", _
lngRecords
End With

MsgBox lngRecords & " appended"

End Sub

The above is what the provider does with the Command anyhow <g>.

Jamie.

--

 

[onedaywhen]

It would be more efficient, in terms of coding and performance, to
simply parse the values out in SQL code

Just let me correct myself there: I think the most efficient way of
executing the proc may be this:

Sub tester3()
Dim Con As Object
Set Con = CreateObject("ADODB.Connection")
With Con
.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data source=C:\DSDATA\Northwind.mdb"

.qryTest "Cat3", "Cat3 described here"
End With
End Sub

The above does not return the rows affected; we need to sink the
Connection object for this, making our performance improvement more
code verbose e.g.

Private WithEvents m_Con As ADODB.Connection
Private m_lngRecordsAffected As Long

Sub tester4()
Set m_Con = New ADODB.Connection
With m_Con
.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data source=C:\DSDATA\Northwind.mdb"

.qryTest "Cat4", "Cat4 narrative"
End With

MsgBox m_lngRecordsAffected & " appended"
End Sub

Private Sub m_Con_ExecuteComplete( _
ByVal RecordsAffected As Long, _
ByVal pError As ADODB.Error, _
adStatus As ADODB.EventStatusEnum, _
ByVal pCommand As ADODB.Command, _
ByVal pRecordset As ADODB.Recordset, _
ByVal pConnection As ADODB.Connection)

m_lngRecordsAffected = RecordsAffected
End Sub

Jamie.

--