VBA remote serial validation

[tuxalot]

Hi guys,

I have an access db that I want to share with users for testing. The functionality I would like to implement is below. Is this possible with Access?

1. first run of db user is presented with form to enter serial. Serial is saved in local table for successive opens.
2. db checks for internet connection.
3. if internet connection, serial entered is checked against values in remote web accessible MySQL database.
4. if serial is found, db opens. If not found, db closes.
5. if no internet connection, db stores serial in local table and increments a numeric field allowing only three opens without validating remote serial.

Seems like a fairly standard piece of code but I can't seem to find an existing solution that I can tailor to fit my needs.

Any nudges in the right direction so I can begin to research how to get this done would be most appreciated.

Thanks to all!

[NeoPa]

You haven't really made clear what a serial is or refers to in this Tux, but otherwise it's been well thought through. That's the first step done. I would suggest the next step is to start coding it and seeing where you get stuck. Then we may be able to assist. There's not really much of a question at this stage though.

[Rabbit]

With your scheme, all I have to do to get around your validation is decrement the counter in the table.

[tuxalot]

You haven't really made clear what a serial is or refers to in this Tux

A unique serial number will be generated by an admin and issued to each authorized user of the database and sent to them via email.

With your scheme, all I have to do to get around your validation is decrement the counter in the table.

Point taken. I'm sure if a user cracked the password to the backend they could do that. I suppose I could upload the value of the counter in the local table when Internet access becomes available and check against a remote value of the same data to see if any monkey business has occurred. I had planned on putting the typical recipe of Access protections in place, i.e. splitting, password protecting front and back ends, making an MDE of the front end, and maybe running the front end through MDE Compiler.

[ADezii]

What, exactly are you not sure of, if it is the checking for an Internet Connection you could try:

'Declare in a Standard Code Module
Public Declare Function InternetGetConnectedState Lib "wininet.dll" (ByRef lpdwFlags As Long, ByVal dwReserved As Long) As Long

Dim lngRetVal As Long
 
lngRetVal = InternetGetConnectedState(0&, 0&)
 
If lngRetVal = 0 Then
  'Code for NO Internet Connection
  '...
Else
  'Code for an Internet Connection
  '...
End If
 

[NeoPa]

As ADezii and I have now both said, what is the question you want answered? Serial is clearly a unique User ID, that's fine and the whole scenario makes sense, but what do you need from us now? You already have the logic laid out, so you're not asking us how to do that.

[tuxalot]

From my original post:

Is this possible with Access?

That was the question. And whether or not the logic seems reasonable. With your post #6 NeoPa, now that is answered. Thanks for that. At least I know my logic makes sense outside of my own head.

Think of it this way. If a student were to come to you with the same question as in my first post, and wanted to know where to go and what topics to research to learn about how to code this, how would you respond?

Any nudges in the right direction so I can begin to research how to get this done would be most appreciated.

In a nutshell, that is what I was asking.

Thanks for the code ADezii.

[NeoPa]

Tuxalot:
Seems like a fairly standard piece of code but I can't seem to find an existing solution that I can tailor to fit my needs.

From your original post I was assuming it wasn't quite as simple as that.

However, if that's what you want then I would answer that the idea is certainly possible in Access, and that the way forward is simply to follow the outline you've already laid out. You know what Serial pertains to in your list so you have all you've asked for I guess.

[tuxalot]

Onward!

I was able to get a linked table setup using ODBC. But after a minute of inactivity the connection is lost. I call this function from a button, but I get a runtime error 3146 "ODBC call failed" at tdf.RefreshLink:

Function relinkTables()
Dim tdf As DAO.TableDef
 
    For Each tdf In CurrentDb.TableDefs
        ' check if table is a linked table
        If Len(tdf.Connect) > 0 Then
            tdf.Connect = "ODBC;Driver=MySQL ODBC 5.1 Driver;SERVER=<myserver>;DATABASE=<mydatabase>;UID=<myuserID>;PASSWORD=<mypassword>;PORT=3306;DFLT_BIGINT_BIND_STR=1;TABLE=clients"
            tdf.RefreshLink '<<< RUNTIME ERROR HERE
        End If
    Next
 
End Function

<placeholders> in connection string are intentional ;)

[ADezii]

tdf.RefreshLink is probably failing because a Connection is not being made via tdf.Connect. Double check your Connection Parameters.

[NeoPa]

It always helps to make the connection manually and see what the .Connect property is set to.

[tuxalot]

NeoPa, I did make the connection manually and copied the connection string from the linked tables description in the properties window. But the connection keeps dropping if there is no activity for a minute or so. Seems like a timeout?

EDIT: On my Windows XP machine I went to Control Panel > Administrative Tools > Data Sources (ODBC) and selected my data source in the File DSN tab. Selected configure and then expand the details window below and on the connection tab put a checkmark in the box entitled "Enable automatic reconnect". So far so good. Connection seems stable now although if does seem that it takes a few seconds longer to show the table when double clicking the linked table in Access.

[ADezii]

Could the Code be falling through and executing the RefreshLink prior to the actual Connection being made?

[tuxalot]

Could the Code be falling through and executing the RefreshLink prior to the actual Connection being made?

This is a possibility ADezii. But for now with the change I made in post #12 it seems to be connecting fine, even following periods of inactivity.

I am wondering how secure this solution is. I know little to nothing about ODBC. Is the user/pass for the remote db exposed on the users machine anywhere? This database will be deployed as a trial to a very limited number of people and is simply being used at this point to get feedback for additional development. Time to study up on security.