Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-14-2019, 09:53 AM   #16
BennyLinton
Newly Registered User
 
Join Date: Feb 2014
Posts: 246
Thanks: 30
Thanked 1 Time in 1 Post
BennyLinton is on a distinguished road
Re: Updating a record through adodb (without ODBC) -getting error

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

BennyLinton is offline   Reply With Quote
Old 10-14-2019, 09:57 AM   #17
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,847
Thanks: 57
Thanked 1,280 Times in 1,261 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Updating a record through adodb (without ODBC) -getting error

Quote:
Originally Posted by BennyLinton View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 10-14-2019, 10:02 AM   #18
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Updating a record through adodb (without ODBC) -getting error

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

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 10-14-2019, 10:02 AM   #19
BennyLinton
Newly Registered User
 
Join Date: Feb 2014
Posts: 246
Thanks: 30
Thanked 1 Time in 1 Post
BennyLinton is on a distinguished road
Re: Updating a record through adodb (without ODBC) -getting error

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.
BennyLinton is offline   Reply With Quote
Old 10-14-2019, 10:05 AM   #20
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,286
Thanks: 13
Thanked 4,111 Times in 4,043 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Updating a record through adodb (without ODBC) -getting error

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.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 10-14-2019, 10:06 AM   #21
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,847
Thanks: 57
Thanked 1,280 Times in 1,261 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Updating a record through adodb (without ODBC) -getting error

Quote:
Originally Posted by BennyLinton View Post
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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 10-14-2019, 10:07 AM   #22
BennyLinton
Newly Registered User
 
Join Date: Feb 2014
Posts: 246
Thanks: 30
Thanked 1 Time in 1 Post
BennyLinton is on a distinguished road
Re: Updating a record through adodb (without ODBC) -getting error

Quote:
Originally Posted by arnelgp View Post
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.

BennyLinton is offline   Reply With Quote
Old 10-14-2019, 10:09 AM   #23
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Updating a record through adodb (without ODBC) -getting error

Dim rs As New ADODB.Recordset
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 10-14-2019, 10:15 AM   #24
BennyLinton
Newly Registered User
 
Join Date: Feb 2014
Posts: 246
Thanks: 30
Thanked 1 Time in 1 Post
BennyLinton is on a distinguished road
Re: Updating a record through adodb (without ODBC) -getting error

Quote:
Originally Posted by arnelgp View Post
Dim rs As New ADODB.Recordset
Still getting same error
BennyLinton is offline   Reply With Quote
Old 10-14-2019, 10:54 AM   #25
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Updating a record through adodb (without ODBC) -getting error

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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
BennyLinton (10-14-2019)
Old 10-14-2019, 10:59 AM   #26
BennyLinton
Newly Registered User
 
Join Date: Feb 2014
Posts: 246
Thanks: 30
Thanked 1 Time in 1 Post
BennyLinton is on a distinguished road
Re: Updating a record through adodb (without ODBC) -getting error

Quote:
Originally Posted by arnelgp View Post
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!
BennyLinton is offline   Reply With Quote
Old 10-14-2019, 11:02 AM   #27
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Updating a record through adodb (without ODBC) -getting error

you're welcome!
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 10-14-2019, 12:59 PM   #28
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,286
Thanks: 13
Thanked 4,111 Times in 4,043 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Updating a record through adodb (without ODBC) -getting error

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.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 10-14-2019, 01:27 PM   #29
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,847
Thanks: 57
Thanked 1,280 Times in 1,261 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Updating a record through adodb (without ODBC) -getting error

Quote:
Originally Posted by pbaldy View Post
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!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 10-14-2019, 06:30 PM   #30
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,656
Thanks: 98
Thanked 1,500 Times in 1,415 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Updating a record through adodb (without ODBC) -getting error

Quote:
Originally Posted by pbaldy View Post
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.

Galaxiom is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Go To record error '2105' - when using ODBC to SQL 2008R2 ronin General 0 03-26-2012 01:39 AM
Run time Error 3219 When Updating Record Set Appu Visual Basic 7 06-23-2008 11:05 PM
Runn Time error updating record -- Help ! Please... k_c_morrissey Forms 0 05-11-2005 06:14 AM
Updating existing record with form causes error? AmmyLea Forms 6 04-02-2004 09:51 AM
Write Conflict Error when Updating Record dungstar Forms 1 11-12-2002 01:10 AM




All times are GMT -8. The time now is 01:55 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World