DBEngine.RegisterDatabase Not Configuring Properly (1 Viewer)

[mistera]

I've searched the internet for what could be my problem, but couldn't find anything, so I'm posting this hoping someone can help.

I have an Access 2007 database with linked tables to SQL Server 2008. In the On Open event for my main menu, if the user's ODBC data source has not been set up on his/her PC, I want to programatically set it up. Here is the code I have:

DBEngine.RegisterDatabase "FinancialPlanningAnalysis", "SQL Server", True, _
    vbCr & "Server=itasql55pr" & vbCr & "Database=FinancialPlanningAnalysis" & vbCr & "Trusted_Connection=Yes"

While the ODBC data source does get set up, it is not set up as a trusted connection. The attached document includes a screen print from the ODBC Data Source Administrator after this code runs. It should be verifying login ID authenticity through Windows NT authenentication.

I thought that stating "Trusted_Connection=Yes" would address this, but apparently it does not. How can I get this to programatically set up the data source with Windows NT authenentication?

 

[Rx_]

OK, there are a few questions to ask to get you started.
First, can you verify that your SQL Server 2008 has a a User set up for SQL Server Security or Integrated Security (Windows security)?

Let me suggest that first, set up a manual ODBC connection with out code. Once the manual ODBC is connected, then followup with the code. The ODBC connection wizard should list your SQL Server in the search.

There are a lot of ODBC fans on this site that I respect. However, I prefer the SQL Native Client. This is a driver fround at the SQL Server 2008 site and must be installed on each user's PC. It allows connection much the same as the ODBC manual method. The code to connect SQL Native client is somewhat straightforward. It can be used as a DSN-Less connection. It is just an option worth looking at. Once the SQL Native Client is installed, the code does not require setting up a ODBC connection on each user's PC. The SQL Native client connection string for a DB with usser name and password at the SQL level would look like this:

strConnectionString = "ODBC;DRIVER=SQL Server Native Client 10.0;" & _
"SERVER=MysqlServer\MySQLInstancer;DATABASE=" & DataBaseName & ";" & _
"UID=" & UID & ";" & _
"PWD=" & PWD & ";" & _
"Table=DBO." & sLocalName & ";Option=3;"

 

[mdlueck]

However, I prefer the SQL Native Client. This is a driver fround at the SQL Server 2008 site and must be installed on each user's PC.

rrrrrrr.....?????

So there is a different ODBC driver than what ships standard in Windows XP for SQL Server? What is the difference? Why do you consider it to be preferred? This is the first I have heard of an additional ODBC driver available for SQL Server.

 

[mistera]

Thanks for the suggestion, but I don’t think that solution will fit my needs. I currently have a .reg file stored on a shared drive. It includes the following information:

Windows Registry Editor Version 5.00
 
[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources]
"FinancialPlanningAnalysis"="SQL Server"
 
[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\FinancialPlanningAnalysis]
"Driver"="C:\\WINDOWS\\system32\\sqlncli10.dll"
"Server"="itasql55pr"
"LastUser"=""
"Database"="FinancialPlanningAnalysis"
"Trusted_Connection"="Yes"

As part of my setup procedure for new users of the Access database application, I ask them to double-click the file which automatically adds the proper ODBC data source to their PC. This becomes a bit of a nuisance when I get calls from new users who forget to follow this step in the procedure or from existing users who got a new machine or a reimaged machine or something of the sort saying that they are getting an ODBC error when they are using the Access database.


When first opening the Access database, my main menu is set up to open. The On Open event for this form does some initial setup. As part of that setup, there is a DLookup to one of the linked tables. So, what I was trying to do was to capture any ODBC errors when executing this statement and have the code automatically set up the ODBC data source on the user’s machine. As mentioned in the initial post, this seems to work except that the DBEngine.RegisterDatabase statement doesn’t set up the verification correctly to use Windows authentication. I thought that saying that it is a trusted connection would address this. Setting it up to use Windows authentication allows the users to bypass any login to the SQL Server database connection making it all seamless to the user who doesn't know that there are linked tables to a SQL Server database.


That said, I have many people who are using this database and I can’t have all of them load drivers on their machines. In fact, at my company, there are a limited number of administrators who have those kind of rights. Most people cannot download anything to their own machines!


I was just looking for a way to limit the number of users calling me with this problem that already has a simple solution – double-click on the .reg file. A more streamlined solution such as what I’m trying to do would be even better.


Any more ideas as to why this isn’t working as expected?

 

[PeterF]

If you need the registry values, why not test if the values exist and if not there create them from VBA. A sample can be found here.

 

[mistera]

Peter, this looks like just the thing I'm looking for. However, I'm not an expert with registry keys, so I'm not sure what I would need to enter for the i_RegKey parameter. In looking at the info in my .reg file (see my last post), I'm not sure how to enter the parameters.

I put the suggested code into a module and tried running the functions RegKeyExists and RegKeyRead, but I don't think I have the key entered correctly because the result is that it can't find it in my registry. I've attached screen prints from the registry editor to show the pertinent keys that are currently in my registry.

So, for example, I entered the following expecting a result of "SQL Server" but instead the key was not found:

regkeyread("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources\FinancialPlanningAnalysis")

Specifically, how would I code the i_RegKey parameter to get this to work?

 

[mistera]

Ignore my previous post. I got it to work by changing "HKEY_CURRENT_USER" to "HKCU". After including the functions in the link provided by Peter, my code is this:

    If RegKeyExists("HKCU\Software\ODBC\ODBC.INI\ODBC Data Sources\FinancialPlanningAnalysis") = False Then
        RegKeySave "HKCU\Software\ODBC\ODBC.INI\ODBC Data Sources\FinancialPlanningAnalysis", "SQL Server"
        RegKeySave "HKCU\Software\ODBC\ODBC.INI\FinancialPlanningAnalysis\Driver", "C:\\WINDOWS\\system32\\sqlncli10.dll"
        RegKeySave "HKCU\Software\ODBC\ODBC.INI\FinancialPlanningAnalysis\Server", "itasql55pr"
        RegKeySave "HKCU\Software\ODBC\ODBC.INI\FinancialPlanningAnalysis\LastUser", ""
        RegKeySave "HKCU\Software\ODBC\ODBC.INI\FinancialPlanningAnalysis\Database", "FinancialPlanningAnalysis"
        RegKeySave "HKCU\Software\ODBC\ODBC.INI\FinancialPlanningAnalysis\Trusted_Connection", "Yes"
    End If

Peter, you are awesome! Thanks so much -- this is exactly what I was wanting to do!