By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,298 Members | 1,691 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,298 IT Pros & Developers. It's quick & easy.

VBA remote serial validation

tuxalot
100+
P: 200
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!
Dec 9 '11 #1
Share this Question
Share on Google+
13 Replies


NeoPa
Expert Mod 15k+
P: 31,707
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.
Dec 9 '11 #2

Rabbit
Expert Mod 10K+
P: 12,421
With your scheme, all I have to do to get around your validation is decrement the counter in the table.
Dec 9 '11 #3

tuxalot
100+
P: 200
Expand|Select|Wrap|Line Numbers
  1. 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.

Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 10 '11 #4

ADezii
Expert 5K+
P: 8,679
What, exactly are you not sure of, if it is the checking for an Internet Connection you could try:
Expand|Select|Wrap|Line Numbers
  1. 'Declare in a Standard Code Module
  2. Public Declare Function InternetGetConnectedState Lib "wininet.dll" (ByRef lpdwFlags As Long, ByVal dwReserved As Long) As Long
Expand|Select|Wrap|Line Numbers
  1. Dim lngRetVal As Long
  2.  
  3. lngRetVal = InternetGetConnectedState(0&, 0&)
  4.  
  5. If lngRetVal = 0 Then
  6.   'Code for NO Internet Connection
  7.   '...
  8. Else
  9.   'Code for an Internet Connection
  10.   '...
  11. End If
  12.  
Dec 10 '11 #5

NeoPa
Expert Mod 15k+
P: 31,707
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.
Dec 10 '11 #6

tuxalot
100+
P: 200
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.
Dec 10 '11 #7

NeoPa
Expert Mod 15k+
P: 31,707
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.
Dec 11 '11 #8

tuxalot
100+
P: 200
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:

Expand|Select|Wrap|Line Numbers
  1. Function relinkTables()
  2. Dim tdf As DAO.TableDef
  3.  
  4.     For Each tdf In CurrentDb.TableDefs
  5.         ' check if table is a linked table
  6.         If Len(tdf.Connect) > 0 Then
  7.             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"
  8.             tdf.RefreshLink '<<< RUNTIME ERROR HERE
  9.         End If
  10.     Next
  11.  
  12. End Function
<placeholders> in connection string are intentional ;)
Dec 11 '11 #9

ADezii
Expert 5K+
P: 8,679
tdf.RefreshLink is probably failing because a Connection is not being made via tdf.Connect. Double check your Connection Parameters.
Dec 11 '11 #10

NeoPa
Expert Mod 15k+
P: 31,707
It always helps to make the connection manually and see what the .Connect property is set to.
Dec 11 '11 #11

tuxalot
100+
P: 200
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.
Dec 11 '11 #12

ADezii
Expert 5K+
P: 8,679
Could the Code be falling through and executing the RefreshLink prior to the actual Connection being made?
Dec 11 '11 #13

tuxalot
100+
P: 200
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.
Dec 11 '11 #14

Post your reply

Sign in to post your reply or Sign up for a free account.