Using Access on a lan

Using Access on a lan

Postby Antonio Linares » Sun Apr 07, 2013 9:26 pm

Does someone have experience using an Access database on a lan ?

thanks,
regards, saludos

Antonio Linares
www.fivetechsoft.com
User avatar
Antonio Linares
Site Admin
 
Posts: 41439
Joined: Thu Oct 06, 2005 5:47 pm
Location: Spain

Re: Using Access on a lan

Postby Rick Lipkin » Mon Apr 08, 2013 1:06 pm

Antonio

I use Access ( 2003 "Microsoft.Jet.OLEDB.4.0".. not 2010-12 ) for a back end on many of my applications using a local Lan .. How can I help you ??

Rick Lipkin
User avatar
Rick Lipkin
 
Posts: 2638
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Using Access on a lan

Postby Antonio Linares » Tue Apr 09, 2013 2:18 pm

Rick,

How many users work on your Access databases simultaneously ?

Is record locking supported ?

Are there any known limitations ?

thanks! :-)
regards, saludos

Antonio Linares
www.fivetechsoft.com
User avatar
Antonio Linares
Site Admin
 
Posts: 41439
Joined: Thu Oct 06, 2005 5:47 pm
Location: Spain

Re: Using Access on a lan

Postby Rick Lipkin » Tue Apr 09, 2013 3:42 pm

Antonio

Record Locking is done by how you define the ADO recordset be it Sql Server, Oracle or Ms Access... notice the Cursortype, CursorLocation and LockType. Note that Ms Access does not seem to have the same Oportunistic Locking problems that .dbfs have in the same environment.

I have shared a sample sql statement because Access handles Dates differently than Sql Server or Oracle..

Typically "MY" Ms Access apps are designed for Local Shared networks with 25 or less users and concurrent users of 10 or 15 at the most ( my personal setup .. sure that number can be significantly higher ) ... peer to peer would be a stretch and I would not recommend that setup.

The reason I like Access is you do not need a connection client and the database can be distributed with the Application. The reason I code my apps with the "xDatabase" variable is so I can easily recompile the same app and run it in Ms Access or Sql Server .. If you have more than 25+ Concurrent users, ( in my humble opinion ) Sql Server is a better option especially if you are coding for ADO.

Also note .. Access databases .mdb can be easily hacked even if you have it password protected. Anyone can copy the .mdb to a flash drive and potentially use it for their own gain. If you do use Ms Access and you have ANY sensitive data like credit card info or User Social Security and Date of Birth .. Please take my advice and encrypt the data on those sensitive tables.

Hope this helps ..
Rick Lipkin

Code: Select all  Expand view


// where .exe started from is default directory //

cFILE  := GetModuleFileName( GetInstance() )
mSTART := RAT( "\", cFILE )
cDEFA  := SUBSTR(cFILE,1,mSTART-1)

SET DEFA to ( cDEFA )

// setup connection string

xDatabase := "
A"  // access
*xDatabase := "
S"  // sql server

If xDatabase = "
A"
   xPROVIDER := "
Microsoft.Jet.OLEDB.4.0"
   xSOURCE   := cDEFA+"
\Travel.mdb"
   xPASSWORD := "
xxxxxxx"
   xCONNECT  := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Jet OLEDB:Database Password='+xPASSWORD
Else
   xPROVIDER := "
SQLOLEDB"
   xSOURCE   := "
RICKLIPKIN-PC\SQLEXPRESS"
   xCATALOG  := "
TRAVEL"
   xUSERID   := "
xxxxxxx"
   xPASSWORD := "
xxxxxxx"
   xConnect  := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD
Endif

oRsAactiv := TOleAuto():New( "
ADODB.Recordset" )
oRsAactiv:CursorType     := 1        // opendkeyset
oRsAactiv:CursorLocation := 3        // local cache
oRsAactiv:LockType       := 3        // lockoportunistic

If xDatabase = "
A"
   cSQL := "
SELECT [AactivEid],[Formno],[TravDate],[Code], "
   cSql += "
[Desc] as Descrip,[Meals],[Mileage],[Perdiem], "
   cSql += "
[Air],[Other],[Misc],[Regist],[Lodging] "
   cSql += "
From [Aactiv] where [Formno] = "+ltrim(str(nFormno))
   cSql += "
and [TravDate] = #"+dtoc(dDate)+"# Order by [TravDate]"
Else
   cSQL := "
SELECT [AactivEid],[Formno],[TravDate],[Code], "
   cSql += "
[Desc] as Descrip,[Meals],[Mileage],[Perdiem], "
   cSql += "
[Air],[Other],[Misc],[Regist],[Lodging] "
   cSql += "
From [Aactiv] where [Formno] = "+ltrim(str(nFormno))
   cSql += "
and [TravDate] = '"+dtoc(dDate)+"' Order by [TravDate]"
Endif

TRY
   oRsAactiv:Open( cSQL,xCONNECT )
CATCH oErr
   MsgInfo( "
Error in Opening Aactiv table" )
   RETURN(.f.)
END TRY
User avatar
Rick Lipkin
 
Posts: 2638
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Using Access on a lan

Postby Antonio Linares » Tue Apr 09, 2013 6:20 pm

Rick,

many thanks for your advice :-)

I have not used Access with ADO except when I developed the AdoRdd and did some simple tests with Access:
viewtopic.php?p=93785#p93785

by that time we found that not all ADO features were available.

Its good to have feedback about Access as probably is a quite common used database (?)
regards, saludos

Antonio Linares
www.fivetechsoft.com
User avatar
Antonio Linares
Site Admin
 
Posts: 41439
Joined: Thu Oct 06, 2005 5:47 pm
Location: Spain

Re: Using Access on a lan

Postby Rick Lipkin » Tue Apr 09, 2013 6:46 pm

Antonio

I can tell you that if you use the newer version of Ms Access with a .accdb, you will need to download the Ms Access Database Engine 2010..

http://www.microsoft.com/en-us/download ... x?id=13255

And the ADO connection String changes to:

Code: Select all  Expand view

xConnect := "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Rick.accdb;Persist Security Info=False"
 


I have decided to just continue to use .mdb since the Microsoft.Jet.OLEDB.4.0 is still native even with Windows 8.

Rick Lipkin
User avatar
Rick Lipkin
 
Posts: 2638
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Using Access on a lan

Postby Antonio Linares » Tue Apr 09, 2013 7:13 pm

Rick,

I have decided to just continue to use .mdb since the Microsoft.Jet.OLEDB.4.0 is still native even with Windows 8


Very good point and very interesting to know it :-)

Also your very usefull information, as usual

Thanks again
regards, saludos

Antonio Linares
www.fivetechsoft.com
User avatar
Antonio Linares
Site Admin
 
Posts: 41439
Joined: Thu Oct 06, 2005 5:47 pm
Location: Spain

Re: Using Access on a lan

Postby nageswaragunupudi » Tue Apr 16, 2013 2:59 pm

Mr Rick

Please clarify/confirm:

1. Can I use mdb with jet oledb even on a PC where MS Access is not installed at all?

2. I understand Jet Oledb does not work on 64-bit PC.
( ACE works both on 32 and 64 bits PCs and works both for mdb and accdb )
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10316
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: Using Access on a lan

Postby Rick Lipkin » Tue Apr 16, 2013 5:07 pm

Rao

1. Can I use mdb with jet oledb even on a PC where MS Access is not installed at all

YES .. generally the file msjet40.dll is located in the \Windows\System32 folder

2. I understand Jet Oledb does not work on 64-bit PC.
( ACE works both on 32 and 64 bits PCs and works both for mdb and accdb )

If you compile your executable with 64 bit it will not use the Jet40 engine .. I have tested my 32 bit executables using .mdb launched from our server on W7 and W8 64 bit desktop machines without any problems.

Using .accdb will need the ACE run-time library loaded on each machine.

Rick Lipkin
User avatar
Rick Lipkin
 
Posts: 2638
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Using Access on a lan

Postby nageswaragunupudi » Tue Apr 16, 2013 10:01 pm

Mr Rick

Thanks
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10316
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: Using Access on a lan

Postby Rimantas » Wed Apr 17, 2013 5:40 am

Rick,

Question also from me ... :) . It seems that access is interesting solution for a small company , with 10-12 pc in lan . Have your experience with a big amount of data at once ? I have in mind solution for production - making , BOM , route ... Routes and BOMs have hundreds records of product - so finishing some production orders it will insert some thousands records at once . Can this work on lan with access ? I'm asking about that , because have negative experience with access , about stability . In one enterprise departmet buyed a solution with Visual Basic + access, program related with hardware. Once at month it was needfull to do repair for access mdb file ... But that can be related to other things , that mdb file used 2 separated programs ...

With best regards !
Rimantas U.
User avatar
Rimantas
 
Posts: 437
Joined: Fri Oct 07, 2005 12:56 pm
Location: Utena , Lithuania

Re: Using Access on a lan

Postby nageswaragunupudi » Wed Apr 17, 2013 5:55 am

Despite Mr Rick's assurance, I am still a bit sceptical.
Why can we not use SqlExpress? It is also free (limit 10GB of data) and has all the features of Sql Server and *almost* the same code we write for Access.

Advantage of Access is that it does not require the installation procedure like SqlExpress
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10316
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: Using Access on a lan

Postby Rick Lipkin » Wed Apr 17, 2013 1:05 pm

Rao and Rimantas

I definitely prefer Sql Server or Sql Express for ADO connected applications .. To my surprise I loaded Sql Express 2012 on my laptop and it connected flawlessly with SqlOleDB in light of the documentation that mentions the preferred method of connecting was the Native SQLNCLI11 provider or the dot net solution.

As far as Ms Access .. I have found it to be stable using the Ole ( ado ) connectivity ( ms jet ) using "client cached" and "opportunistic locking" recordsets rather than the dot net or VB way. I am re-writing an old VB 6 application re-using the legacy .mdb and during my development, I have treated the access database very badly with ugly abrupt shut downs and even several times with a ctrl-alt-delete and NOT had one single problem with corruption or data loss. Again, I think that is due to how I am using ADO and "client cached" recordsets with "opportunistic locking" and not actually maintaining a full connection to the database.

For relatively small applications to be used on a local lan or as a stand alone environment .. using Ms Access is a good "programming" choice especially using ADO because you can code it once ( and for the most part ) modify ( only ) the connection string and re-compile if you want to migrate the data to Ms Sql Server or Oracle.

One last thought .. since oledb is still supported ( in 32 bit ) on any Windows OS including Windows 8 .. it seems such an efficient way to deliver an application with no setup or run-time client on the desktop... up until Microsoft decides to completely diminish oledb in favor of its own native Sql client, ACE or dot net.

Rick Lipkin
User avatar
Rick Lipkin
 
Posts: 2638
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Using Access on a lan

Postby Rimantas » Wed Apr 17, 2013 2:17 pm

Rick Lipkin wrote:Rao and Rimantas

As far as Ms Access .. I have found it to be stable using the Ole ( ado ) connectivity ( ms jet ) using "client cached" and "opportunistic locking" recordsets rather than the dot net or VB way. I am re-writing an old VB 6 application re-using the legacy .mdb and during my development, I have treated the access database very badly with ugly abrupt shut downs and even several times with a ctrl-alt-delete and NOT had one single problem with corruption or data loss. Again, I think that is due to how I am using ADO and "client cached" recordsets with "opportunistic locking" and not actually maintaining a full connection to the database.

For relatively small applications to be used on a local lan or as a stand alone environment .. using Ms Access is a good "programming" choice especially using ADO because you can code it once ( and for the most part ) modify ( only ) the connection string and re-compile if you want to migrate the data to Ms Sql Server or Oracle.

Rick Lipkin


Thanks to you , Rick , for the answer !
Rimantas U.
User avatar
Rimantas
 
Posts: 437
Joined: Fri Oct 07, 2005 12:56 pm
Location: Utena , Lithuania


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: karinha and 33 guests