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

BennyLinton · Oct 11, 2019

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:

Code:

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 · Oct 11, 2019

Here's my SQL Server stored procedure:

Code:

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 · Oct 11, 2019

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 · Oct 14, 2019

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)?:

Code:

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

theDBguy · Oct 14, 2019

BennyLinton said:
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)?:

Code:

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 · Oct 14, 2019

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

Code:

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 · Oct 14, 2019

BennyLinton said:

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

Code:

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 · Oct 14, 2019

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 · Oct 14, 2019

BennyLinton said:
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 · Oct 14, 2019

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 · Oct 14, 2019

BennyLinton said:
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 · Oct 14, 2019

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

theDBguy · Oct 14, 2019

BennyLinton said:
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 · Oct 14, 2019

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 · Oct 14, 2019

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

Code:

  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 · Oct 14, 2019

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

theDBguy · Oct 14, 2019

BennyLinton said:
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 · Oct 14, 2019

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 · Oct 14, 2019

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 · Oct 14, 2019

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.

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

Registered User.

Registered User.

Wino Moderator

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

I’m here to help

I’m here to help

Wino Moderator

Registered User.

I’m here to help

..forever waiting... waiting for jellybean!

Registered User.

Wino Moderator

Similar threads

Users who are viewing this thread