ODBC connection failed

[Alec]

Hi All,

I am currently trying to link in Access 97 to a table in a MSSQL 7
server. Initially the link is fine, however, when I close the access
database and re-open it from the same network lacation on a different
machine, it tells me that the ODBC connection to 'SQL Server Name'
failed.

How can I create the linked table permenant no matter what machine the
networked database is opened on?

TIA

Alec Christie
alec_e_christie(at)hotmail(dot)com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Rich P]

When you copy the application to another workstation and the ODBC link
doesn't work, most likely the other workstation doesn't have the DSN to
your sql server set up.

Here is some code that automatically creates a System DSN for Sql Server
on an MS Windows workstation. You can place this routine in a standard
module and call it from a startup form in the Form_Load event.

'************************************************* *******
Private Const REG_SZ = 1
Private Const HKEY_LOCAL_MACHINE = &H80000002

Private Declare Function RegCreateKey Lib "advapi32.dll" _
Alias "RegCreateKeyA" (ByVal hKey As Long, _
ByVal lpSubKey As String, phkResult As Long) As Long

Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias
"RegSetValueExA" (ByVal hKey As Long, ByVal _
lpValueName As String, ByVal Reserved As Long, _
ByVal dwType As Long, lpData As Any, ByVal _
cbData As Long) As Long

Private Declare Function RegCloseKey Lib "advapi32.dll" _
(ByVal hKey As Long) As Long
Sub createSystemODBCdsn()
'Microsoft Knowledge Base Article - 184608
Dim DataSourceName As String
Dim DatabaseName As String
Dim Description As String
Dim DriverPath As String
Dim DriverName As String
Dim LastUser As String
Dim Regional As String
Dim Server As String
Dim lResult As Long
Dim hKeyHandle As Long

'Specify the DSN parameters.

DataSourceName = Name of your DSN
DatabaseName = Name of the database in Sql Server
Description = simple discription - Not require
DriverPath = "C:\WINNT\System32\SQLSRV32.DLL"
LastUser = "SA" 'if SA doesn't work, use your UserID
Server = Name of the Sql Server or IP address
DriverName = "SQL Server"

'Create the new DSN key.

lResult = RegCreateKey (HKEY_LOCAL_MACHINE, _
"SOFTWARE\ODBC\ODBC.INI\" & DataSourceName, hKeyHandle)

'Set the values of the new DSN key.

lResult = RegSetValueEx(hKeyHandle, "Database", 0&, _
REG_SZ, ByVal DatabaseName, Len(DatabaseName))
lResult = RegSetValueEx(hKeyHandle, "Description", 0&, _
REG_SZ, ByVal Description, Len(Description))
lResult = RegSetValueEx(hKeyHandle, "Driver", 0&, REG_SZ, _
ByVal DriverPath, Len(DriverPath))
lResult = RegSetValueEx(hKeyHandle, "LastUser", 0&, _
REG_SZ, ByVal LastUser, Len(LastUser))
lResult = RegSetValueEx(hKeyHandle, "Server", 0&, REG_SZ, _
ByVal Server, Len(Server))

'Close the new DSN key.

lResult = RegCloseKey(hKeyHandle)

'Open ODBC Data Sources key to list the new DSN in the ODBC Manager.
'Specify the new value.
'Close the key.

lResult = RegCreateKey(HKEY_LOCAL_MACHINE, _
"SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", hKeyHandle)
lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&, _
REG_SZ, ByVal DriverName, Len(DriverName))
lResult = RegCloseKey(hKeyHandle)

End Sub

'************************************************* *******

Just fill in the parameters above where it says "Specify Parameters".
This will create your dsn and an entry in the registry. You can go to
Start\Settings\Control Panel\Administrative Tools\Data
Sources(ODBC)\System DSN tab to see your new dsn.

You can also go to Regedit to

HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources

and you will see your dsn there too.
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Alec]

Rich,

Thanks so much, that worked a treat, i was worrying that I would only be
able to use the database from one machine!

Thanks again!

Alec Christie
alec_e_christie(at)hotmail(dot)com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Rebecca]

Alex & Rich,

The Registry keys you establish in the posted VB code can also
be created by a setup.exe program. The ODE tools in an access developer's
edition include a wizard to produce a slick installation package for an
Access front end. It has a section for adding registry keys.
I've used that route successfully for porting to other machines on our LAN.
But I have a follow up question about how to set up
the rest of the configuration to get the same portability across the internet.

Here's my special trouble spot: the connection has to be made
to an SQL Server "named instance". These run on non-default port numbers;
I know the fixed port number and the server has an internet DNS hostname.
I've used cliconfg.exe running on the remote machine to manually establish
a working connection via TCP/IP by entering the hostname and port number
into the appropriate controls. This builds a "server alias" on the client
so that the ODBC Data Source can connect. I've observed the registry keys
this adds, and I found only two:

HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\ Client\ConnectTo
contains a <server alias> key with value "DBMSSOCN,<hostname>,<port#>"

HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\ Client\TDS
contains a <server alias> key with value "7.0"
{it's SQL 2000, though - should I worry?)

I then use this <server alias> in place of the <server name> in the
ODBC.INI keys. I'm getting 99.44% of the connection effect.
On the positive side, the "SQL Server ODBC Data Source Test"
sequence on such clients PASSES!- the ODBC data source believes it exists,
and I believe it since it rejected bad passwords and allowed only
good passwords - which only the server could have told it.
I'm assuming that this test sequence also verified that the network
library version numbers are compatible. It reported them so it must
have considered them as test criteria.

Now if the .mdb or .mde file would work I probably wouldn't be posting.

It doesn't. When I run the .mde file, the SQL Server login dialogue
takes 30 seconds after I enter the password and then
coughs up an error string from the Socket library Connect() saying
that the SQL Server could not be found.

Reviewing I see the Linked Table has a Connect string:
"ODBC;DSN=<odbcsource>;..." Which names my validated data source,
and also gives the right database name (same code works on the LAN).

The ODBC Data Source passes its connection test. There is no encryption
specified for the SQL server traffic. Do you pros have any idea why the
full client won't connect?

I'd dearly like to ship installation CDs that make these Registry keys
(and then clean them up when you uninstall). I believe they should work.
They don't.

The only small diff I've noted is that when I bring up cliconfg.exe on
a client whose keys were built by setup.exe, I don't see TCP/IP as
an enabled protocol for the SQL Network Client. The ODBC connection test
could only have used TCP/IP. It is happy, so why not Access 97?
The firewall lets both TCP and UDP get through on the port# in question.

The one time I got a good connection front to back was in early experiments.
I'd tried using "hostname:port#\SQLServerInstanceName" as the ODBC SQL
Server name. It didn't know what to make of this. So I used cliconfg
to make an alias whose spelling exactly matched that string. It worked!
So I went home and wrote tidier looking aliases into my keys and came
back only to find egg on my face. Does cliconfg.exe do something the
key settings alone cannot do? If so, is there a command line way to get
cliconfg to do this during an installation sequence?

Thanks for any hand holding you can offer.

Greg (& Rebecca)

[Rich P]

Disclaimer: I am not the Maestro of any of this stuff :).

But using ODBC accross the internet/intranet - kinda sounds like
re-inventing the wheel, ie: you have a Good Year steel belted radial
tire, but are looking to develop a Fred Flintstone wheel (with all due
respect). Across the net I would stick with Asp(x) and ADO for
interfacing with Sql Server. Far more reliable than ODBC.

Matter of fact, the only reason I even use ODBC at all (for Sql Server)
is for the end users who want to see live tables in Access, without
warranty. ODBC has real limited bandwidth compared to ADO, plus, the
connection is constantly open, unlike ADO.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Rebecca]

Rich P wrote:

Disclaimer: I am not the Maestro of any of this stuff :).

But using ODBC accross the internet/intranet - kinda sounds like
re-inventing the wheel, ie: you have a Good Year steel belted radial
tire, but are looking to develop a Fred Flintstone wheel (with all due
respect). Across the net I would stick with Asp(x) and ADO for
interfacing with Sql Server. Far more reliable than ODBC.
Granted, but this is a legacy Access97 ap being "upsized" (no sizes
are actually changing) so that the small fixed number of users can
escape from the LAN. The database is relational, and their internet
connectivity to the server is excellent. But I guess we're hitting
"unreliable" aspects of the ODBC - SQL Server link up.

As to car analogies - I'd just say that we've got a vintage Caddy that
we need to take on a long distance drive in an era of high gas prices.
We *could* replace the engine and discard 2/3rds of the steel, but the
ride would never be the same! Those linked tables and bound forms
seem to work fine when I build the ODBC Data Source by hand on each
client - I just can't do that for each user and I want to know how to
automate it reliably.
Matter of fact, the only reason I even use ODBC at all (for Sql Server)
is for the end users who want to see live tables in Access, without
warranty. ODBC has real limited bandwidth compared to ADO, plus, the
connection is constantly open, unlike ADO.

Rich