sql: new dsn on windows 7

[tom kroto Member Find all posts View Profile Close tom kroto]

I bought a new desktop with Windows 7 64bit. I transferred all necessary software for SQL tools and pbcc5.2 to the new computer. I had a program
that was running under XP. I changed the DNS to match the correct path for the new computer. I was able to run the program successfully on the
new computer. Here is a copy of the DSN.

[ODBC]
DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
ReadOnly=1
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=excel 12.0
DriverId=1046
DefaultDir=C:\users\tom kroto\Documents
DBQ=C:\users\tom kroto\Documents\checkbook7.xlsx

I've been attempting to create a new DSN. This new DSN is exactly like the one that was working except for a change to the Excel workbook. This
is the error that I received -- a connection could not be made using the file data source parameters entered.

I went back to the old computer and went through the same steps. Instead of the error message I did get to the screen that says ODBC Microsoft
Excel setup. It had the Excel 12.0 listed and I selected the workbook. It worked fine.

I could easily just take the original DSN and make a copy for the new workbook with minor changes. But I want to know why this is failing on the
new desktop. It can't be because the driver is not present since the first program worked perfectly. Any ideas? Thank you.

[Michael Mattias Member Find all posts View Profile Close Michael Mattias]

>I've been attempting to create a new DSN.

By editing the file to create a FILEDSN? (You say same steps result in, "Instead of the error message I did get to the screen that says ODBC Microsoft ..." , except you show the file, which you generally don't see if you are going thru the provided utilities)

If it's not what you are doing, what you might want to do is use whatever "Wizard" is provided (on XP: Control Panel, Adminstrator Functions, ODBC data sources, "new") and see how the "filedsn" structure may have changed from prior versions of Windows.

Also you may want to make sure you are logged on as "tom kroto" when you do this. You may be running into some security restrictions.

Another thing to try (and this is a WAG), try making the DBQ file spec unqualified ("DBQ=checkbook7.xlsx") and see if it uses the 'DefaultDir' setting.

Or yet another thing to try: build your own "DSN-less" connection string and try connecting with that.

Potentially very helpful web site for you: http://www.connectionstrings.com . Heck, they might even have some new info there specific to Windows/7.

MCM

[tom kroto Member Find all posts View Profile Close tom kroto]

failure of wondows 7 wizard file dsn

Thank you for your reply. First off I would like to say that it was the wizard that failed on Windows 7. I'm not an expert but I had created about 6 dsn files on XP. A very simple solution to my problem was to copy the DSN that I knew worked and make a few alterations. Here is an example of the DSN that I created for Windows 7.

[ODBC]
DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
ReadOnly=1
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=excel 12.0
DriverId=1046
DefaultDir=C:\users\tom kroto\Documents
DBQ=C:\users\tom kroto\Documents\purchases.xlsx

in fact I got the program to work.
My theory is that the wizard on windows 7 was trying to create a 64 bit driver connection. Note that in the DSN the driver ID is 1046. I did some research on the Internet and could not find a 64 bit driver. Maybe that is the reason the wizard failed? My concerns are not of a practical nature since I got the program to work. My concerns are theoretic in nature since I wonder what my desktop with Windows 7 may be lacking.