Creating ODBC Data Source

[wpatterson]

I am new and evaluating Advanced Installer 1.8.

I am trying to set up a new professional project that should be very simple. I simply need to copy a Microsoft Access database file, create a shortcut and create a system ODBC data source to connect to a SQL server.

I can figure everything out except creating the system ODBC data source. I am used to doing it manually by specifying a name, server, username/password and database.

How do I set this up with Advanced Installer? I try to add a new ODBC datasource, but I don't see where to add all of the information. I looked up the info on the same ODBC datasource using regedt32, but it doesn't specify things like the password in the data fields.

Thanks!

[Cata]

Hi Bill

What you need should be easy to do with Advanced Installer. The trick is that Windows Installer supports a generic system of name/value for specifying data source attributes.

What you need is to know which names would correspond to the values you need (server, user, password, etc.). I suggest trying the obvious ones. Also, you may to find out that information from the ODBC driver documentation.

If you still have troubles, please let me know: our ODBC expert is back on Monday and he can help you better.

Best regards,
Cata

[wpatterson]

I tried various combinations and I obviously don't have it defined properly.

I get and ODBC error 6, saying that it can't find a registry value.

I don't know what to do next, since I've tried all of the name and value items that I can thnk of.

[wpatterson]

I noticed that if you aren't logged in as a local administrator, you don't seem to get any pulldown menu items to select the ODBC driver. Once I recognized this, I could set the ODBC Data Source driver to SQL Driver.

No I get ODBC Error 8 - invalid keyword-value pairs. This must mean that the one or more of the values that I'm setting are not valid. I looked in the registry on a system with the System ODBC that I'm trying to install and it lists most of the values, except it doesn't mention password and lists just lastuser, not user.

I have tried various combinations of names for the username, like user, loginid, id and it the installation blows up with the ODBC error 8.

Are there any examples for creating an ODBC data source? All I should need to input is the server, database, description, user and password.

Thanks!

[wpatterson]

Is your ODBC expert in today? If so, can they please take a look at my request/problem setting up an ODBC connection?

Thanks!

[Cata]

1: Excerpt from help files:
You can find out the attributes necessary for the selected datasource in two ways:
First, you can install your datasource on your local machine using Administrative Tools - ODBC. Then use Registry Editor to see the registry keys and values from HKLM-Software-ODBC-ODBC.INI key. Under this key there is a key for every datasource installed on your computer. Identify the registry key corresponding to the datasource you want to install on the target machine. Note the name and values from that key. These are the attributes you have to add in your package (not neccessary all - some of them might get a default value from the driver).
Another way is to install your datasource on your local machine as a File DSN - and then find out the attributes names and values from that file (on Windows XP you can find it in Program Files\Common Files\ODBC\Data Sources)

2.From my research I suppose the name of the interested attributes are:
Server - for server name
Description - for description
Database - for database name
UID - for username
PWD - for password
Apparently the case doesn't matter but I don't know until I test it and this can be done only on a real server.

[wpatterson]

Thanks for your help. I built another MSI file, specifying the following data items for a SQL Server ODBC system connection (server, database, description, uid, pwd).

I still get an error when installing using this MSI file:

Error configuring ODBC data source APMS, ODBC error 8: Invalid keyword-value pairs. Verify that the file APMS exists and that you can access it.

I have tried both uppercase and lowercase UID and PWD and nothing seems to work.

I need to get this working and I'm spending 10 times as much work on it as I should need to.

Do you have any ideas for me to try?

Thanks!

[Cata]

Bill - did you try to find out the necessary attributes on your own machine as I explained at point 1?

Also, please make sure the db server is started while doing the install - the driver won't create the datasource otherwise.

Cata

[wpatterson]

Yes, I have looked at the local information in the registry for the existing system DSN that I'm trying to re-create. It lists the name, description, server, database, driver and Lastuser only. It says nothing about the username or password.

Thanks!

[Cata]

Darn. I am afraid I can't think of anything else.

I don't have a copy of SQLServer, so I suggest going and getting the info on the name/value pairs for the user and password from the product documentation or support, since they are specific to it.

If I find out anything else, I'll post it here.

Regards,
Cata

[wpatterson]

I created the File DSN that you suggest in your help text and created the keyword-value pairs from this list, adding a PWD for password field.

Still I get the same error!

I don't see how anyone can use this product to create ODBC data sources if you can't supply an example of how to connect to a data source like SQL server. All I need to do is install a Microsoft Access database, install a shortcut to it and create the system DSN data source. This should take about 10 minutes of time to set up and yet it it a week later and I have nothing to show for my efforts.

Thanks!

[wpatterson]

I did some more checking about other programs to use to create a System DSN data source and from what I can tell, it looks like you can't create a DSN data source that specifies a password. It seems that this is considered a security risk.

Thus, it may not be possible to create an ODBC connection to a SQL Server database using SQL Server authentication, because if you can't connect to the the database because no password can be specified, then perhaps the ODBC data source can not be created. You did say that it needed to be online for the data source to be created.

Thanks!