The Actual ODBC Driver for Access is the first commercial driver to connect to Access databases on a Mac without the need for complicated 'bridge' or 'router' solutions. A setup assistant, diagnostic utility, and on-line help are included with the easy-to-use installer. The Access ODBC Driver is a powerful tool that allows you to connect with live Microsoft Access data, directly from any applications that support ODBC connectivity.
Welcome to the p2p.wrox.com Forums. You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions.
This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting.
By you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free. Hi, I have setup a new database using Access 2003 that accesses an ODBC database which requires a username and password. Because the database is for basic level users, we do not want them to know the password.
The database can be programmed to use the password in the background, but having very very basic indeed VBA skills, I do not know where to place the code. I have discovered from the Help files that you can use the DefaultUser and DefaultPassword, but this does not mention a server (on which the database is stored) and whereabouts in the form's code that I should write the script (for example, General, Declarations). I did try anyway, but it did not work - I don't really know if this is the correct property anyway!! Can anybody help me on this one, please?
I will be really grateful if you can. All the best, Roly. Which ODBC database are you connecting to? Each will use its own ODBC driver, so the connection string syntax (which can include the user name and passsword) will vary. You connect to an ODBC database using either: a) an ODBC DSN-Less connection string (by setting the ConnectionString property of an ADO Connection object), or b) an ODBC DSN (Data Source Name) set up using the ODBC Data Source Administrator in Windows at Start - Programs - Administrative Tools - Data Sources (ODBC). (on Windows XP PRO) Neither of these approaches is exactly 'very, very basic indeed', however.;) HTH, Bob.
Hi Roly, Once we figure out what database sever you are using, I'm thinking the easiest thing for you to do (no coding involved) would be to store a user name and password in the DSN itself, and then link to the databse server through the Access interface. To do that, you would select: File - Get External Data - Then in the Link dialog box, click the 'Files of Type' drop down list and select 'ODBC Databases'. This will launch the 'Select Data Source' dialog where you will see listed all the Machine(user and system) and File DSNs that you have defined on your system using the ODBC Data Source Administrator. That way anyone who has permissions to open the Access application will automatically have permission to access the database server back-end. How many client machines are involved?
Hi Bob, Thanks for your messages. The database is runs on sql server - using an ODBC DSN set up through ODBC Data Source Adminstrator (I set it up on computer). We cannot store the username and password in the DSN becassue it is our main MIS here and needs to be protected, hence the reason you must log on.
I have heard about this ADO connection string, in my research into this issue, but cannot code it myself, nor do I know where to put the code. Any help will be really appreciated. Kind regards, Roly. Got a couple of functions that you may be able to use.
The first (AttachTableMSJet) is used for dynamically attaching MS Access/Jet tables to your Access database. The second (AttachTableClientServer) is used for attaching Client-Server tables (including SQL Server) tables to your database.
And they both work - tried 'em out myself first! Hi Roly and Warren, With the help of Warren's Client-Server Attach Function (cool code Warren) I believe we've done it:). I made 3 modifications to the code that should get you what you need Roly.
No DSN involved. Change One: Warren, I got rid of DAO to simplify things for Roly, and added the following which is one way to 'detach linked table(s) using ADOX' (caveat: no duplicate table names allowed!!) ' If link exists, delete it On Error Resume Next Set tbl = cat.Tables(strTableNameParm) If Err.Number = 0 Then cat.Tables.Delete strTableNameParm Else Err.Clear End If Change Two: I created a new sub procedure that calls Function AttachTableClientServer, passing an ADO recordset of all the user tables in the SQL Server db, instead of just the name of a single table. That way, Roly can link to multiple tables at start-up.
So there is a new loop in the AttachTableClientServer Function and the parameter list has been changed. Change Three: I dealt with the issue of needing to refresh the Database Window (so the new links are visible) after the code runs at start-up by adding: Application.RefreshDatabaseWindow OK Roly.
You now have the ADO connection you've been asking for. Here's how you can use it. Create a new standard module in your app and paste the code below into it. Name the module basStartUp, or something. Set references to the Microsoft ADO Ext. 2.x for DDL and Security and the Microsoft ActiveX Data Objects 2.x Library (whichever versions of these two dlls are installed on your system) 3. Create a new Marco and name it AutoExec (an AutoExec macro will always run when the database loads.) 4.
Set the macro's properties as follows: Action: RunCode Function Name: RetrieveSQLServerUserTables I created RetrieveSQLServerUserTables as a fucntion, even though it doesn't return any values, simply because Macros can't call Sub Procedures. You could also call RetrieveSQLServerUserTables from the Load event of a startup form if you have one. Now when a user opens the database, the code in the module will run and establish links to all the your user tables on SQL Server. Since the user name and password a stored in the 'Jet OLEDB:Link Provider String' property of the table link requested, users won't be prompted to log on. Thanks again Warren, and post with further questions Roly if you have any.