icedtea97

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

GMan_NC

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?

icedtea97

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

loquin

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)

GMan_NC

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.

icedtea97

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

GMan_NC

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

icedtea97

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 & "' "

GMan_NC

For a SELECT statement using a recordset, you set the locking you wish, in your first example you used adLockOptimistic for optimistic locking.

icedtea97

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?

GMan_NC

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.

icedtea97

ok GMan_NC,
if i have problem, i will post my thread again.
thanks for you help.
icedtea97

GMan_NC

Please do, we are here to help.

icedtea97

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

GMan_NC

Correct, for the INSERT and UPDATE statements

icedtea97

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

thanks

GMan_NC

No, for select you should use the Recordset object or the Recordset object in conjunction with the command object.

icedtea97

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

GMan_NC

No, SELECTing the records won't be a problem, Editing may be a problem.

icedtea97

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

thanks,
icedtea97