running an access append query with ado from VB?

  • Thread starter Christina Haller
  • Start date
C

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
 
B

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
 
C

Christina Haller

Thank you so much Brendan.

It works perfectly!

Greez
Christina
Brendan Reynolds said:
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)

Christina Haller said:
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
Click to expand...
Click to expand...
 
Top