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

theDBguy

I’m here to help
Staff member
Local time
Today, 02:20
Joined
Oct 29, 2018
Messages
21,358
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.
Hi. Not sure how you got there. I don't have anything like that set up. All I need to make sure is each machine has a ODBC driver installed, which should come with Windows by default anyway. I can't say the same for OLEDB though. In any case, did you look at any of the links I posted earlier? How about the code suggested by either Paul or Arnel?
 

BennyLinton

Registered User.
Local time
Today, 02:20
Joined
Feb 21, 2014
Messages
263
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

The line "Set Me.Recordset = rs" is giving an error: The object you entered is not a valid recordset property.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:20
Joined
May 7, 2009
Messages
19,170
Dim rs As New ADODB.Recordset
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:20
Joined
May 7, 2009
Messages
19,170
Code:
Private Sub Form_Load()


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

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

con.ConnectionString = CONN_STRING
con.Open

With rs
    .ActiveConnection = cn
    .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open "selectPeopleAll"
End With

Set Me.Recordset = rs

End Sub
 

BennyLinton

Registered User.
Local time
Today, 02:20
Joined
Feb 21, 2014
Messages
263
Code:
Private Sub Form_Load()


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

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

con.ConnectionString = CONN_STRING
con.Open

With rs
    .ActiveConnection = cn
    .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open "selectPeopleAll"
End With

Set Me.Recordset = rs

End Sub

Arnel... thanks with one small change "cn" to "con" it works perfectly now!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:20
Joined
May 7, 2009
Messages
19,170
you're welcome!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:20
Joined
Aug 30, 2003
Messages
36,118
I'm glad you have a solution but could you expand on this .reg file that you have to maintain on each user's computer? Apparently I'm not the only one that's never had to bother with it. I've got closer to 100 users of different apps, and I've never touched most of their computers.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:20
Joined
Oct 29, 2018
Messages
21,358
I'm glad you have a solution but could you expand on this .reg file that you have to maintain on each user's computer? Apparently I'm not the only one that's never had to bother with it. I've got closer to 100 users of different apps, and I've never touched most of their computers.
Hi Benny. I'd be interested to hear more about it as well, if you don't mind. Thanks!
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:20
Joined
Jan 20, 2009
Messages
12,849
I'm glad you have a solution but could you expand on this .reg file that you have to maintain on each user's computer? Apparently I'm not the only one that's never had to bother with it. I've got closer to 100 users of different apps, and I've never touched most of their computers.

User and Machine DSNs are stored in the registry. They must be present on each machine in some setups.

I always use a File DSN. The DSN file is no longer required once the table is linked as everything required to connect is included in the connection string in the database.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:20
Joined
Oct 29, 2018
Messages
21,358
User and Machine DSNs are stored in the registry. They must be present on each machine in some setups.

I always use a File DSN. The DSN file is no longer required once the table is linked as everything required to connect is included in the connection string in the database.

My question is, is this specific to ODBC only? I wonder why the OP is focused on avoiding ODBC and apparently uses OLEDB instead. Thanks!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:20
Joined
Aug 30, 2003
Messages
36,118
I've always used a file DSN or DSN-less code. Would user or machine DSN's be handled with a reg file directly or manually via the UI?
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:20
Joined
Jan 20, 2009
Messages
12,849
I've always used a file DSN or DSN-less code. Would user or machine DSN's be handled with a reg file directly or manually via the UI?

Long time since I have used a DSN in a connection string. I can't even remember using a User DSN but I have had to work with Machine DSNs.

Last time was for a third party program that required a DSN be specified in its settings. I had to distribute and run the reg file and it was a pain. Not so bad now since modern Group Policy can do registry updates very simply. The program converted to specifying a connection string in the meantime. I don't think DSNs are used directly very much any more because they are clunky.

The connection string contains a reference to the name of the DSN.

Code:
DSN = dsnName;

A File DSN can be read directly at run time.

Code:
FileDSN = pathToDSNfile;

This can be useful to change all the ODBC links on a whole network by simply editing the one file DSN. However it isn't like a target database moves around all the time so not really that useful. It could also be seen as a security vulnerability. You would certainly want to make sure it couldn't be changed by unauthorised people.

In this technique you need to build the connection string manually in Access. When a file DSN is used to link a table in Access via the Wizard, it creates a DSNless connection string by reading the information from the fileDSN which is then no longer required.

I link Access with the file DSN so the connection ends up DSNless. We did migrate our all our databases onto one SQL Server recently and I had to edit the Connect properties of all the tables in a lot of Access front ends and Excel connections. Not so hard in code but it took a while to run it against big tables. Then all the Access front ends had to be compacted.
 

BennyLinton

Registered User.
Local time
Today, 02:20
Joined
Feb 21, 2014
Messages
263
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

If within the dataview I wanted the ID# double-clicked to bring up a form with more detail on each person, how could I go about that? I have the double click successfully launching a simple detail form, but I don't know where to place the calling of the stored procedure and how to ensure that the popup form displays the right person... thanks in advance!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:20
Joined
May 7, 2009
Messages
19,170
add this code to your pop-up form:
Code:
Option Compare Database
Option Explicit

Dim frm As Form

Private Sub Form_Close()
    frm.Recalc
End Sub

Private Sub Form_Open(Cancel As Integer)
    Dim rs As ADODB.Recordset
    Set frm = Forms![I][B]name_of_parent_form[/B][/I]
    Set rs = frm.Recordset
    rs.Filter = "ID = " & frm("ID").Value
    Set Me.Recordset = rs
End Sub
 

BennyLinton

Registered User.
Local time
Today, 02:20
Joined
Feb 21, 2014
Messages
263
By parent form do you mean the name of the calling form which is a subform that has the field that receives the double-click?

A double click on that form has code to open the detail form:

Code:
Private Sub NBCCIId_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDetail"
End Sub

I put the below in the popup:

Code:
Dim frm As Form

Private Sub Form_Close()
    frm.Recalc
End Sub

Private Sub Form_Open(Cancel As Integer)
    Dim rs As ADODB.Recordset
    Set frm = Forms!dbo_MHF_Standard_subform
    Set rs = frm.Recordset
    rs.Filter = "NBCCIId = " & frm("NBCCIId").Value
    Set Me.Recordset = rs
End Sub

But I receive the error: Cannot find the referenced form "dbo_MHF_Standard_subform"
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:20
Joined
May 7, 2009
Messages
19,170
reference by MainForm!SubForm:

Set frm = Forms!MainFormName!dbo_MHF_Standard_subform
 

BennyLinton

Registered User.
Local time
Today, 02:20
Joined
Feb 21, 2014
Messages
263
Changed to but same message:

Code:
Set frm = Forms!frmSwitchboard!dbo_MHF_Standard_subform
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:20
Joined
May 7, 2009
Messages
19,170
what if you add a Form on the tail:

Set frm = Forms!frmSwitchboard!dbo_MHF_Standard_subform.Form
 

Users who are viewing this thread

Top Bottom