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.

 

[CaliKidd]

Boyd and Bob,

Ok, thanks for clarifying the matter. I didn't realize it was a matter that I could not use Access as a web back-end. I thought perhaps it was that I should not if the performance was poor.

Oh well, this kind of puts me back to square one on the database, which shouldn't be too much of a problem once I get my hands on the software because this login/logout database is so simple in its design and function, but now I'm back to the dreaded connection string question for MySQL or SQL Express.

Thanks for all your help guys. I'll try and locate the new database software and I'll checkout the link you gave me, Bob, to see if I can find the appropriate connection string there.

Why do simple things have to be so complicated?

 

[xenou]

It's not my area of expertise but I should think you *could* create an ajax request to send a username to a (php/perl/python/asp.net) script on your web server. Your web server can then handle the connection to the DB (a server side script). But - do your users really need you watching over their shoulders this way?

ξ

 

[SydneyGeek]

There's nothing stopping you using Access as the front end to a MySQL database.

I found this link in a web search: http://forums.mysql.com/read.php?65,140090,140090
You will be asked to register and, once that is completed, you will get a PDF white paper with a reasonably detailed walk-through on converting the Northwind database to MySQL. The PDF also has links to a number of other resources that you might find useful.

Denis