Updating a record through adodb (without ODBC) -getting error

[BennyLinton]

I'm getting what may be a datatype error with the following VBA on a form with 3 textboxes and a button for updating a record. In one textbox i type the ID# and in the other two corrections to a person's name:

Private Sub UpdatePeople_Click()

Dim strCUser As String
strCUser = CurrentUserName
Dim con As adodb.Connection
Dim cmd As adodb.Command
Dim rs As adodb.Recordset

Set con = New adodb.Connection
Set cmd = New adodb.Command

con.ConnectionString = CONN_STRING
con.Open
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "updatePeople"

cmd.Parameters.Append cmd.CreateParameter("@NBCCIId", adInteger, adParamInput, 40, Me.NBCCIId)
cmd.Parameters.Append cmd.CreateParameter("@firstName", adVarChar, adParamInput, 40, Me.firstName)
cmd.Parameters.Append cmd.CreateParameter("@lastName", adVarChar, adParamInput, 40, Me.lastName)

cmd.Execute

End Sub

I'm getting the error:

"Method or member not found" on the "Me.NBCCIId"

 

[BennyLinton]

Here's my SQL Server stored procedure:

CREATE Procedure [dbo].[updatePeople]
(
@NBCCIId int,
@firstName varchar(50),
@lastName varchar(50)
)

As
Update MHFs 
	set MHFs.firstName = @firstName,
	MHFs.lastName = @lastName
Where MHFs.NBCCIId = @NBCCIId

 

[pbaldy]

The error implies that isn't the name of the textbox, so I'd start with double checking that. I've had times where it was correct but I had to retype it.

 

[BennyLinton]

I got the Edit form working fine now... One more thing, what would be a good strategy to code my VBA to call this stored procedure below to populate a gridview (datasheet view)?:

CREATE PROCEDURE [dbo].[selectPeopleAll]
AS
SELECT * FROM MHFs

 

[theDBguy]

I got the Edit form working fine now... One more thing, what would be a good strategy to code my VBA to call this stored procedure below to populate a gridview (datasheet view)?:

CREATE PROCEDURE [dbo].[selectPeopleAll]
AS
SELECT * FROM MHFs

Hi. I may be way off here but how about using a View instead of a SP? Just curious...

 

[BennyLinton]

I will probably switch it to a view.... that's the easy side... the hard part is the VBA something like this:

Private Sub Form_Load()


Dim strCUser As String
strCUser = CurrentUserName
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set con = New ADODB.Connection
Set cmd = New ADODB.Command

con.ConnectionString = CONN_STRING
con.Open
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "selectPeopleAll"
   
cmd.Execute


Set Me.Recordset = rs

End Sub

 

[theDBguy]

I will probably switch it to a view.... that's the easy side... the hard part is the VBA something like this:

Private Sub Form_Load()


Dim strCUser As String
strCUser = CurrentUserName
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set con = New ADODB.Connection
Set cmd = New ADODB.Command

con.ConnectionString = CONN_STRING
con.Open
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "selectPeopleAll"
   
cmd.Execute


Set Me.Recordset = rs

End Sub

Hi. Are you saying you don't want to create linked tables?

 

[BennyLinton]

Yeah trying to get away from ODBC because of maintaining all the registry holdings of specific databases for each user's machine... the code you posted makes sense but all my fields are just returning #name?

 

[theDBguy]

Yeah trying to get away from ODBC because of maintaining all the registry holdings of specific databases for each user's machine... the code you posted makes sense but all my fields are just returning #name?

Hi. When you say you're "trying to avoid ODBC," what are you assigning to CONN_STRING?

 

[BennyLinton]

Yes I have a connection string stored as part of a module.

Public Const CONN_STRING As String = "Provider=SQLxxxx;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MHF;Data Source=xxxxx"

I'm using it successfully for SPs that add and update data on forms.

 

[theDBguy]

Yes I have a connection string stored as part of a module.

Public Const CONN_STRING As String = "Provider=SQLxxxx;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MHF;Data Source=xxxxx"

I'm using it successfully for SPs that add and update data on forms.

Hi. And are you sure that connection string is not using ODBC? Just curious...


With regards to your original question, I'll have to do some digging.

 

[BennyLinton]

I'm sure there is no ODBC connectivity in place on this specific database.

 

[theDBguy]

I'm sure there is no ODBC connectivity in place on this specific database.

Hi. I think you're right. I just looked it up and it seems you're maybe using OLEDB instead of ODBC.

 

[theDBguy]

Hi. Here's a couple of previous discussion links I'm hoping you can find some answers from:

Using ADO command object to retrieve recordset and return value from stored procedure

and

Return stored procedure data to ms access table

 

[pbaldy]

If you want the form to be editable, this type of thing should work. Even if you don't, it's a lot simpler.

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = OpenDatabase("", False, False, "ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=ServerName;DATABASE=CabTS;Trusted_Connection=Yes")
  
  strSQL = "SELECT * FROM tblMedallionTypes"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Set Me.Recordset = rs

 

[BennyLinton]

I'm trying to get away from ODBC and linked tables.

 

[theDBguy]

I'm trying to get away from ODBC and linked tables.

Hi. I could understand about linked tables but not sure I understand about avoiding ODBC. If what I said earlier was true, you're simply using another provider (OLEDB) in place of ODBC. Using ODBC doesn't mean you need to use a linked table. I have an app that connects to a hosted SQL Server, and I don't have a linked table to it. Instead, I use ODBC to pull in the data from the Server.

 

[arnelgp]

Private Sub Form_Load()


Dim strCUser As String
strCUser = CurrentUserName
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set con = New ADODB.Connection
Set cmd = New ADODB.Command

con.ConnectionString = CONN_STRING
con.Open
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "selectPeopleAll"

set rs = cmd.Execute


Set Me.Recordset = rs

End Sub

 

[BennyLinton]

But I have 20 users and 16 databases each littered with linked tables that the ODBC requires a .reg file loaded on each machine for each database. Maintenance nightmare.

 

[pbaldy]

In 20+ years I don't think I've ever dealt with (or even heard of) a .reg file required by linked tables, and I use tables linked to SQL Server the vast majority of the time.