Using Access on a lan

[Antonio Linares]

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

thanks,

[Rick Lipkin]

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

[Antonio Linares]

Rick,

How many users work on your Access databases simultaneously ?

Is record locking supported ?

Are there any known limitations ?

thanks!

[Rick Lipkin]

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

[Antonio Linares]

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 (?)

[Rick Lipkin]

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

[Antonio Linares]

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

[nageswaragunupudi]

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 )

[Rick Lipkin]

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

[nageswaragunupudi]

Mr Rick

Thanks

[Rimantas]

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 !

[nageswaragunupudi]

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

[Rick Lipkin]

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

[Rimantas]

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 !