Charlotte Foust
charlotte.foust at gmail.com
Thu Jan 23 22:53:52 CST 2014
You won't get any arguments from me on ADO. I used it extensively in Access 2000 but had to fall back to DAO as a job requirement for the next 10 years. Now I'm building databases intended to be maintained by trained users who have absorbed the concepts, so I'm using DAO because it's easier for them to understand. If I could wrap everything in classes, I'd dust off my ADO chops and knock their socks off; but I have to create something they can maintain and expand going forward. I don't think I can drum basic database theory and design into them and expect them to handle two different object models as well. I can dream though ... Charlotte On Thu, Jan 23, 2014 at 6:22 PM, Jim Lawrence <accessd at shaw.ca> wrote: > Hi all: > > I know I promised to answer back before last Christmas but things have > been busy to say the least. Now that I have a few uninterrupted minutes I > will answer. > > First the preamble; MS Access is probably the best quick database rapid > design application ever built. I have been looking for over thirty years > and nothing has proved better. Access's one weakness is it database. It has > always been too small, lacking security and is unstable to say the least. I > know many will point out their prowess on keeping a full MS Access running, > in spite off, other than because off, hosting many clients over unstable > networks. > > It was back in the late nineties I realized that the MDB was a toy in > comparison to the strength of the Front End so to that end I moved the all > subsequent applications to use ADO database connection even to the > associated MDB. > > I worked on government contracts for years and could not get anyone to > listen or try MS SQL. The entire provincial government was a closed Oracle > shop. Finally, supposedly, for a small job, I was given a nice new Dell > server box, 7 drives and 8 GB of RAM along with a copy of MS SQL. The only > problem was that the version of MS SQL had only a dozen connection > licenses...hardly enough for a serious application. > > I finally figured out a way to extend a MS Access application. First > dropped was the concept of "bound data", as it just wasn't possible. One > user would easily consume all licences. Next, requirement was that the new > application, that I was assigned, had to be provincial wide. This meant > that users had to be able to use the product, in real-time, in every urban > and rural setting, on hardware connect from fiber-optic to a 56K modem and > at sometimes, disconnected. Another challenge was to find a way have every > computer using the application not having to have a tech either walk the > staff through a complex installation procedure or go on site. > > At that point I settled on ADO data and data connection. Every Windows > computer since Windows95 up to Windows8.1, has ADO pre-installed. (The new > version is 64 bit so this might cause complications?) This meant that ODBC > did not have to be used and DAO was for the most part DOA. Below is a list > of a few basic connection function: > > ... > ' Microsoft connection string...standard security MS SQL > gstrConnection1 = "Provider=SQLOLEDB;" & _ > "Initial Catalog=Registration;" & _ > "Data Source=MyMSSQLServer;" & _ > "Integrated Security=SSPI" > > ' Test connection string and handle errors > Set mobjConn = New ADODB.Connection > > mobjConn.ConnectionString = gstrConnection1 > 'mobjConn.CursorLocation = adUseClient 'adUseServer > mobjConn.Open > ... > > '---------------------------------------------------- > > ... > ' Microsoft connection string...standard security Oracle, tnsnames.ora > gstrConnection2 = "Provider=msdaora;" & _ > "Data Source=MyOracleServer.gov.bc.ca;" & _ > "User Id=" & myUsername & ";" & _ > "Password=" & myPassword > > ' Test connection string and handle errors > Set mobjConn2 = New ADODB.Connection > > mobjConn2.ConnectionString = gstrConnection2 > mobjConn2.Open > ... > > '---------------------------------------------------- > > ... > strMDBVersion = CurrentDb.Version > strDBNameLocation = CurrentDb.Name > strDBName = Dir(strDBNameLocation) > strDatabaseLocation = _ > Replace(strDBNameLocation, strDBName, "MyMDBServer.mdb") > > ' We will assume that we are connecting to a remote > ' backend MDB Datbase. In this case we will just connect > ' another DAO database called 'MyMDBServer'. > > gstrConnection3 = "Provider=Microsoft.Jet.OLEDB." & strMDBVersion & " ; " > & _ > "Persist Security Info=False; " & _ > "Data Source=" & strDatabaseLocation > > ' Test connection string and handle errors > Set mobjConn3 = New ADODB.Connection > > mobjConn3.ConnectionString = gstrConnection3 > mobjConn3.Open > ... > > '------------------------------------------------------- > > The beauty of using ADO connection standards is that you can connect to > virtually any standard data source, one at a time or all together and no > previous specific computer configuration is necessary. One of the major > problems with auto downloading a single MS Access copy, to hundreds of > computers on a network, is the potential for conflicts. Connecting via ADO > asynchronised resolves these problems. > > End of part 1 > > Jim > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >