Local DAO To Remote ADO Conversion Help

[CaliKidd]

I am developing an Excel VBA app that connects to an Access database to track login/logout activity.

Although my Excel VBA are decent, my Access VBA skills are almost non-existent as I've not spent much time in the Access Object model yet, so please forgive my ignorance.

In any case, I've built a basic Access 2007 database that currently sits on my D:/ drive. I am currently connecting to it without problems via a DAO connection, which I understand may be better suited for the older MDB database.

What I would like to do is:
1. Convert to the connection to the database to an ADO connection.
2. Move the database to my webhost (let's say it's www.abc123.com)
3. Make sure the connection is secure (I'm assuming this means a username and password, but perhaps more?)
4. Include a simple error trap that detects if the connection fails and displays a msgbox that says "Connection failed. Check your internet connection."
5. The goal is to make an automated login entry to the database, then drop the connection. Same with logout. It would be an automated entry that drops the connection once the record is saved.

Can anybody help me with that?

I am using the following DAO code which I found elsewhere on this forum:

Sub Login_To_Access()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = OpenDatabase("D:\UserLogins.accdb")
Set rst = db.OpenRecordset("Login History")  'Add table name
    With rst 'Use field names
        .AddNew
        .Fields("LogInDate") = Date
        .Fields("LogInTime") = Time
        Set rst = Nothing
        Set db = Nothing
    End With
End Sub

Additionally, going forward, I understand that Access is slow and may not be the best database to use in a network setting, especially if a lot of users are accessing it. If that is true, are there better, low-cost alternatives? I'm assuming something like SQL would be expensive for an individual like myself? My webhost is currently a Windows platform, but I could move my domain to a Linux platform if necessary.

 

[xenou]

Typically web hosting (even cheap web hosting) will provide either MySQL databases (unlimited) or SQL Server Databases (at least one).

Both of these will be more robust than Access across networks. But you can use Access too -- I would assume that the mdb will be accessed by the app running on the server - so it's not a remote connection per se. Your web host will hopefully have some instructions in this regard. Access is not best for true remote connections as lost connections during a write are a cause of database corruption.

If you want to try SQL Server a free edition is available: SQL Server Express (you could install this on your development machine).

For information on using ADO:
http://www.erlandsendata.no/english/index.php?t=envbadac
http://www.datawright.com.au/excel_resources/excel_access_and_ado.htm
http://www.w3schools.com/ado/default.asp

I also got a lot out of these books:
http://www.amazon.com/Automating-Microsoft-Access-Sales-Gunderloy/dp/0789732440
http://www.amazon.com/Excel-2007-Programmers-Reference-Programmer/dp/0470046430 (good ado chapter)

Note that if you use a 2007 Access file format (.accdb) with ADO you will need to use the ACE provider, not the JET provider. With an mdb you can stick with Jet - as you will find in many examples on the web.

 

[CaliKidd]

Thanks for the info and links. I'll check them out in detail this evening and hopefully I can find the specific VBA code needed to do this.

The Excel VBA app I am working on is designed to work locally on a person's PC, but I want it to log in/out of a remote database hosted on the internet so I can monitor activity. If I stay with Access, then it will be an .accdb, not a .mdb, but knowing that other low/no-cost db options are available (mySQL, SQL Express) is helpful.

I welcome anyone else's input on this post too.

 

[boblarson]

which I understand may be better suited for the older MDB database.

Don't know where you got that from but DAO is alive and well and VERY WELL in the newer ACCDB file formats. So, no problem in continuing to use it.

 

[CaliKidd]

Bob,

I spent all last night surfing the net and there seems to be a raging debate about ADO vs DAO, whether DAO is obsolete/unsupported by MS, etc. It seems like a lot of this debate is being possibly fueled by some misinformation about DAO and it's future, so thanks for straightening it out.

In any case, I find it all terribly confusing, primarily because I don't know the finer details of either connection method to know which is better. I've just been working off the ASSUMPTION that newer means better, but I'm questioning that assumption now.

I'm a simple guy, lol. I just want to make the "best" DB connection from my Excel app to an Access 2007 (.accdb) database that I host on my website, but not sure how to go about accomplishing this since I am unfamiliar the Access object model.

I've decided that since DAO is working for me now, I'm going to stay with it. I'll revisit the issue once I'm more educated on the topic. However, in the meantime, I'm still not clear about how to code the VBA for the DAO connection string if the database sits out on the internet as opposed to my hard drive. Can you help me out? I searched a lot of sites last night and never found the answer...

This is the line in question (with the db path and filename):

Set db = OpenDatabase("D:\UserLogins.accdb")

What would it be if the database was on an internet URL?

 

[boblarson]

I would use DAO since it is native to Access but the thing you have to realize is that there is no DAO reference (as named such) in Access 2007 and above (using the accdb files). The reference, if you choose to use it, is

Microsoft Office 12.0 Access Database Engine Library

or for 2010

Microsoft Office 14.0 Access Database Engine Library

and that is what you need a reference to if you are using early binding and want to connect with ACCDB/ACCDE/ACCDR files. If you use late binding, you don't need to change a thing.

 

[boblarson]

Oh, and while I am at it - I have found that for connecting to things like SQL Server, Oracle, MySQL, etc., that ADO is probably the best for that. But within Microsoft Office apps? You can use ADO and I have done so. But DAO might have the slight edge there as there are some things that ADO can't do that DAO can. So, it really depends on what you are trying to accomplish and when it comes down to it, if you can get it done with whatever one you are using, regardless, then it should be fine. There is some personal preference flexibility there.

 

[CaliKidd]

Bob,

Thanks again for your expertise. I am using the Microsoft Office 12.0 Access Database Engine Library reference now, so no change is needed unless I upgrade to Office 2010 at some point. For now, I plan to stick with Access, but whether I stay with it or not will depend on it's performance over the internet.

If I decide to use a database other than Access, then I'll explore the ADO option, but for now I'm holding onto the DAO connection method since it's working for me.

The only unresolved issue is what change I need to make to my connection code when I move my Access off my local D:/ drive and out to my internet webhost. I'm not clear what change I need to make so the Excel app can find the remote database. Any ideas?

 

[HiTechCoach]

Bob,
The only unresolved issue is what change I need to make to my connection code when I move my Access off my local D:/ drive and out to my internet webhost. I'm not clear what change I need to make so the Excel app can find the remote database. Any ideas?

The short answer is that you cannt. You can only connect to a JET/ACE database on a LAN. If you wan tto place a JET/ACE database on a remote web site you will not be able to conenct to it remotely.

If your end goal is to place the database on a web site or in the cload then I would recommend that you you skip Access as a back end and go with mySQL or MS SQL Server Express. Note that the back end you use will limit which web hosting services you can use. It can also have an impact on cost.

 

[boblarson]

The only unresolved issue is what change I need to make to my connection code when I move my Access off my local D:/ drive and out to my internet webhost. I'm not clear what change I need to make so the Excel app can find the remote database. Any ideas?

Just so you know - Access doesn't work all that well in a situation like this. You might be able to use one of these connection strings:
http://www.connectionstrings.com/access-2007

but again - connecting to Access via a web host is a bit different than just connecting locally. I've not attempted it myself. So, in order to do that, ADO might be the better option. Hard for me to say.

EDIT: Nevermind - Boyd has a better, and more knowledgeable answer than mine.