Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > Database and Reporting > can i perform multiuser database for access


Reply
 
Thread Tools Display Modes
  #1  
Old 04-22-2004, 10:35 AM
icedtea97 icedtea97 is offline
Newcomer
 
Join Date: Apr 2004
Posts: 21
Default can i perform multiuser database for access


hello guys,
i'm very new here.i'm facing one problem here.
i want to know whether i can use access to perform multiuser database or not.
cause i have search through the internet for the answer but the answer is not clear.
i'm have develop two application to perform the client/server project but it just not working.
i have try DSN and also Adodb connection but i still get error.
i want to know it is possible to do it.
is there any example that i can refer to?

regards,
icedtea97
Reply With Quote
  #2  
Old 04-22-2004, 10:38 AM
GMan_NC's Avatar
GMan_NC GMan_NC is offline
Proud Daddy

Forum Leader
* Expert *
 
Join Date: Sep 2002
Location: North Carolina, USA
Posts: 1,962
Default

It is possible to use Access in a multi-user environment. However, depending on the number of users, this may not be the best option.

Let's start with the basic problem first.

How many users will be using the database concurrently?
What errors are you getting when attempting to connect to the database?
__________________
A morning without coffee is like something without something else.
Reply With Quote
  #3  
Old 04-22-2004, 10:52 AM
icedtea97 icedtea97 is offline
Newcomer
 
Join Date: Apr 2004
Posts: 21
Default

i have only one client that make my system only have two user(one server one client).

error message that i get is like:
run time error '3709'
operation is not allowed on an object referencng a closed or invalid connection

and also some other error

thanks,
icedtea97
Reply With Quote
  #4  
Old 04-22-2004, 10:52 AM
loquin's Avatar
loquin loquin is offline
Google Hound

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,402
Default

And, while Access can be used on a network, you can't use it over the internet (without creating a "server" app in ASP or winsock)
__________________
Lou
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown
Reply With Quote
  #5  
Old 04-22-2004, 10:54 AM
GMan_NC's Avatar
GMan_NC GMan_NC is offline
Proud Daddy

Forum Leader
* Expert *
 
Join Date: Sep 2002
Location: North Carolina, USA
Posts: 1,962
Default

loguin makes a good point. So if you are running over the internet, then we need to do something else.

The error message you are getting means you are trying to open a recordset on a connection object that is either invalid or not open. Let's see some code.
__________________
A morning without coffee is like something without something else.
Reply With Quote
  #6  
Old 04-22-2004, 11:00 AM
icedtea97 icedtea97 is offline
Newcomer
 
Join Date: Apr 2004
Posts: 21
Default

i'm not running through the internet. is on a cross cable connection(LAN)

my connection is like this:

Public my_cn As New ADODB.Connection
Public my_cmd As New ADODB.Command

my_cn.Open "DSN=Clientmydatabase;uid=;pwd=;database=my_temp_database"
Set my_cmd.ActiveConnection = my_cn

when i add the code is like this:
Dim SQL As String
Dim Rs As New ADODB.Recordset
SQL = "INSERT INTO original_table([name], age,[subject]) VALUES ('" & Replace$(name, " '", "' '") & "' , '" & age & "' , '" & Replace$(subject, " '", "' '") & "' ) "

my_cmd.CommandText = SQL
Rs.CursorLocation = adUseClient
Rs.Open my_cmd, , adOpenDynamic, adLockOptimistic

Set Rs = Nothing
Reply With Quote
  #7  
Old 04-22-2004, 11:08 AM
GMan_NC's Avatar
GMan_NC GMan_NC is offline
Proud Daddy

Forum Leader
* Expert *
 
Join Date: Sep 2002
Location: North Carolina, USA
Posts: 1,962
Default

I'll assume all that code is in the same Sub or Function

Okay, since you are running an action statement (INSERT INTO), you do not open a recordset, you just execute the command
Code:
my_cmd.CommandType = adCmdText my_cmd.Execute Set my_cmd = Nothing
__________________
A morning without coffee is like something without something else.
Reply With Quote
  #8  
Old 04-22-2004, 11:14 AM
icedtea97 icedtea97 is offline
Newcomer
 
Join Date: Apr 2004
Posts: 21
Default

well,
i have more sub and function which i have the Insert,Update,Select.

all i have to change is like this without openning my database.

is this mean there will be no locking as i execute that code?

one more thing i have one statement that select sum(total) from the table will this cause any locking to the database?
check_big = "SELECT sum(big) FROM " & table_name & " WHERE name= '" & name & "' "
Reply With Quote
  #9  
Old 04-22-2004, 11:22 AM
GMan_NC's Avatar
GMan_NC GMan_NC is offline
Proud Daddy

Forum Leader
* Expert *
 
Join Date: Sep 2002
Location: North Carolina, USA
Posts: 1,962
Default

For a SELECT statement using a recordset, you set the locking you wish, in your first example you used adLockOptimistic for optimistic locking.
__________________
A morning without coffee is like something without something else.
Reply With Quote
  #10  
Old 04-22-2004, 11:28 AM
icedtea97 icedtea97 is offline
Newcomer
 
Join Date: Apr 2004
Posts: 21
Default

GMan_NC,
i want to know two PC insert record into one database, how does access handler it.because it should be able to handler right?like allow server finish process then onlt it allow client to process.

is it in this way?
Reply With Quote
  #11  
Old 04-22-2004, 11:33 AM
GMan_NC's Avatar
GMan_NC GMan_NC is offline
Proud Daddy

Forum Leader
* Expert *
 
Join Date: Sep 2002
Location: North Carolina, USA
Posts: 1,962
Default

Ok, sorry for the confusion. You should have no problem with 2 users inserting records into the database at the same time. Problems may occur when 2 users try to edit the same record at the same time, however.
__________________
A morning without coffee is like something without something else.
Reply With Quote
  #12  
Old 04-22-2004, 11:38 AM
icedtea97 icedtea97 is offline
Newcomer
 
Join Date: Apr 2004
Posts: 21
Default

ok GMan_NC,
if i have problem, i will post my thread again.
thanks for you help.
icedtea97
Reply With Quote
  #13  
Old 04-22-2004, 11:39 AM
GMan_NC's Avatar
GMan_NC GMan_NC is offline
Proud Daddy

Forum Leader
* Expert *
 
Join Date: Sep 2002
Location: North Carolina, USA
Posts: 1,962
Default

Please do, we are here to help.
__________________
A morning without coffee is like something without something else.
Reply With Quote
  #14  
Old 04-22-2004, 11:48 AM
icedtea97 icedtea97 is offline
Newcomer
 
Join Date: Apr 2004
Posts: 21
Default

GMan_NC
i need to change my code to this:

my_cmd.CommandText = SQL
my_cmd.CommandTYpe = adCmdText
my_cmd.Execute
and close this two line:
'Rs.CursorLocation = adUseClient
'Rs.Open my_cmd, , adOpenDynamic, adLockOptimistic

set my_cmd = Nothing
Reply With Quote
  #15  
Old 04-22-2004, 11:51 AM
GMan_NC's Avatar
GMan_NC GMan_NC is offline
Proud Daddy

Forum Leader
* Expert *
 
Join Date: Sep 2002
Location: North Carolina, USA
Posts: 1,962
Default

Correct, for the INSERT and UPDATE statements
__________________
A morning without coffee is like something without something else.
Reply With Quote
  #16  
Old 04-22-2004, 11:56 AM
icedtea97 icedtea97 is offline
Newcomer
 
Join Date: Apr 2004
Posts: 21
Default

ok.
how about for SELECT? is it using the same method?

thanks
Reply With Quote
  #17  
Old 04-22-2004, 11:58 AM
GMan_NC's Avatar
GMan_NC GMan_NC is offline
Proud Daddy

Forum Leader
* Expert *
 
Join Date: Sep 2002
Location: North Carolina, USA
Posts: 1,962
Default

No, for select you should use the Recordset object or the Recordset object in conjunction with the command object.
Code:
Public my_cn As New ADODB.Connection my_cn.Open "DSN=Clientmydatabase;uid=;pwd=;database=my_temp_database" Dim SQL As String Dim Rs As New ADODB.Recordset SQL = "SELECT * FROM tblSomeTable" Rs.CursorLocation = adUseClient Rs.Open SQL, my_cn, adOpenDynamic, adLockOptimistic Set Rs = Nothing
__________________
A morning without coffee is like something without something else.
Reply With Quote
  #18  
Old 04-22-2004, 12:05 PM
icedtea97 icedtea97 is offline
Newcomer
 
Join Date: Apr 2004
Posts: 21
Default

GMan_NC,
just now you have mention there might been problem for SeLECT if the two PC are updating the same page.

how do i prevent it from crash?
thanks
Reply With Quote
  #19  
Old 04-22-2004, 12:08 PM
GMan_NC's Avatar
GMan_NC GMan_NC is offline
Proud Daddy

Forum Leader
* Expert *
 
Join Date: Sep 2002
Location: North Carolina, USA
Posts: 1,962
Default

No, SELECTing the records won't be a problem, Editing may be a problem.
__________________
A morning without coffee is like something without something else.
Reply With Quote
  #20  
Old 04-22-2004, 12:13 PM
icedtea97 icedtea97 is offline
Newcomer
 
Join Date: Apr 2004
Posts: 21
Default

ok! i got it.
i will try it now but will let you know the result tomorrow cause it midnight now.

thanks,
icedtea97
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
error: Unregconize database format after convert database deell Database and Reporting 3 12-21-2003 10:29 PM
Database question boogieoompa Tech Discussions 1 10-26-2003 07:32 PM
Database to Database lothos12345 Database and Reporting 1 03-03-2003 05:52 PM
Still write to database problem cballe Database and Reporting 0 08-08-2002 04:22 AM
Database problem for database Guru`s cballe Database and Reporting 5 08-08-2002 02:24 AM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->