Solved: Only running MyExcel.xls on named machine?

[Ken Puls]

Thanks Ken, a very detailed and well explained response!, i have studied the code and i don't really understand the ADO programming, however i stepped thru the code watching what was called and when and reading your comments...

ADO essentially it can act as an interface to your database so that you can read the records, change them, etc... It actually allows you to do more, in my experience, to Access than you can through the Access user interface alone. The best way to learn is doing exactly what you're doing though.

Questions:
1. whats the difference between a standard and class module?

A class module essentially acts as a blueprint for a new object. You can define your own objects, give them properties, methods, events, etc... Honestly, this doesn't actually need to be in a class module, but once you have a class module built, you can easily export it and reuse it in other projects. What you've got is a copy of one of mine, culled down to only use the specific methods and properties we need for this.

2. what if the user doesn't have Access installed?

That's the beauty of ADO. It doesn't matter. I even have an example on my site of how to create a new Access database via ADO which will work if you don't have access on your PC.

3. what does SQL stand for?

Structured Query Language. It's an extremely powerful language that allows you to return recordsets, create/manipulate database tables and more. It's used extensively in database applications.

4. when stepping thru the code it looks like an array of names is being created in excel for examination and then cleared afterwards, is that correct? if so would it ever be visible in excel?i.e if an error occurred preventing the code from completing

Excellent! That is exactly what is happening. We request a recordset from the database, convert it to an array, and then inspect it. The array conversion is not strictly necessary, but I do it to make sure I can just dump it into a worksheet should I want to do that. If an error occured right now (with no error handling), the error message would show, but you'd never see the data in Excel. (It will only show up in a worksheet if you tell it to go there.)

5. once a machine name and username have been entered how do i restrict that user from recording another record in the db for himself at another machine? i.e only one entry per user!, i have had trouble with a matrix i created where people would give their login to friends or unauthorised people to access the file at their machines ( originally i used to trap usernames by hard coding the names in an IF statement for an inputbox)

We'll need to adjust the code a bit to do that. I'll see what I can do for you there.

6. if the db was ever deleted would that mean no-one could enter the workbook again as the wb open has the close statement if no db found?

Sot of correct. You could open the workbook with macros disabled, comment the Close line in the code, save it and reopen it. This might be a good reason to lock your VB Project if you have any other VBA'ers in your organization. For reference, the same is also true if you were using an Excel workbook as yoru database.

7. if a user tries to open the file and it is currently in use it opens as read only would this cause a problem with the code or would it continue as normal?

It won't cause any issues with the code, as it is querying the database for info. While the database can be configured for exclusive use, that is extra work that is not necessary, and therefore has not been done.

Once again thanks for your continued support!

Happy to. I'll get back to you with a mod for the user checking.