Hi - I have an access 2003 FE database connecting to a MySQL BE database which I need to roll out to about 40 users and then create views for the data based upon their log in user name. I can do this at the server end - but I also need to have their user name inserted in a field for each new record that they create while in Access. Is there any way that I can get (and then use) the information that they entered in the connector from within Access? I can't find how Access calls the connector and then uses it. Any help greatly appreciated!

Paul

What about a passthrough query, "SELECT CURRENT_USER();"?

But if you're automating the connection, are you actually using unique username or IP for each user?

Thanks BananaRepublic for getting back so quickly- I've just made the passthrough query and it returns the Current_User info - hoorah! - but it wants me to specify a connection each time I run it, whereas I want it to run using the current connection. I note I can specify the connection string in the properties but that defeats the object.

I tried this in code:

Dim strSQL As String
strSQL = "SELECT CURRENT_USER();"
DoCmd.RunSQL strSQL
Debug.Print strSQL

but I got an error message about 'A runSQL action requires an argument consisting of a SQL statement'. Am I missing something here? I want to be able to use the CURRENT_USER value as the default value in a text box on a form ...

I want to use a DropBox shared folder to distribute the database and that means that everyone needs the same unaltered version - I can't have local tables or differences in each version. All the sorting then has to be done using views on the server. Each user would get their own login and password.

I'd just add the connection string but make it incomplete. Access will be able to re-use an existing connection if there's already one open so you won't get prompted.

Something like this:



will suffice.


I'm not sure why RunSQL didn't work but I also almost never use it as well; using either Execute/OpenRecordset method or on rare occasions, OpenQuery.

Thanks very much BR - the pass through query with the new properties still asks for a user name and password (doesn't re-use the existing connection) - but I'll put the results of the query on the switchboard so that it should only ask once when it first opens. Thanks for all your help - I think I can get everything working now! Cheers

Paul

The connection can be reused only if all 3 values given above are identical matches. Is that the case and how are you making the existing connection?

An alternative to take more control is to initiate the connection yourself then throw it away. I have such sample in this thread.

I've been using the graphical connector that comes with the 5.1.6 installation - there are boxes that allow the user to specify their username and password - the port, server and database are all filled in for them and I've used that info in the properties of the pass through query. I've just tried to use the query as the basis of a form and have been flooded with requests to fill in the connector - dunno what's going on there. I'll try to use the access wizard to set the connection properties of the query and let you know if I've made a mistake there - gotta put my daughter to bed as well!

Gotcha - the sample linked above will show you how to construct the complete string in VBA so you don't have to use the default dialog that you get with MySQL ODBC and thus simplify everything.

Couldn't see your sample ...

Ran the wizard for the connection properties and got this generated:

ODBC;Driver=MySQL ODBC 5.1 Driver;SERVER=XXX;DATABASE=XXX;PORT=3306;DFLT_BIGINT_BIND_STR=1

and all is fine - no repeated requests for connections! Phew - looks like it's really picky about the info and needed the port and DFLT info. I've just helped put my daughter to bed and made a form that can use the CURRENT_USER info as the default - so we're there! Thank you so much for all your help. I'd still be interested to see your sample. Well done for being a 5,000 poster and a great help.

It's the 2nd post -- See if this helps?

Glad you were able to find the right partial connection string - I don't remember having to have to add port and I certainly don't remember a option named "DFLT_BIGINT_BIND_STR" - I've been always using the option number -- Options=XXXXX but maybe it has supported both variants. BTW, be aware that for better compatibility with Access, you should have option 2 (Return Matching Rows) enabled as well.

But I'm very glad you are sorted. (IMG:style_emoticons/default/smile.gif)