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

[theDBguy]

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]

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]

Dim rs As New ADODB.Recordset

 

[BennyLinton]

Dim rs As New ADODB.Recordset

Still getting same error

 

[arnelgp]

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]

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]

you're welcome!

 

[pbaldy]

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 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]

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]

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]

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]

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.

DSN = dsnName;

A File DSN can be read directly at run time.

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]

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]

add this code to your pop-up form:

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]

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:

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

I put the below in the popup:

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]

reference by MainForm!SubForm:

Set frm = Forms!MainFormName!dbo_MHF_Standard_subform

 

[BennyLinton]

Changed to but same message:

Set frm = Forms!frmSwitchboard!dbo_MHF_Standard_subform

 

[arnelgp]

what if you add a Form on the tail:

Set frm = Forms!frmSwitchboard!dbo_MHF_Standard_subform.Form

 

[BennyLinton]

Same error