Connect without DNS..?

[Kent Johnson]

Hi all,

I have a Access application with two linked SQL-server tables.
How can I distribute this application without asking the user to set up an
DSN-datasource on their clients.
I can not use 'Trusted Connection'.

/Kent J.

 

[Douglas J. Steele]

Check what I have at http://www.accessmvp.com/DJSteele/DSNLessLinks.html

While the example there is Trusted Connection, all you should need to do is
change one line of code from

tdfCurrent.Connect = "ODBC;DRIVER={sql server};DATABASE=" & _
DatabaseName & ";SERVER=" & ServerName & _
";Trusted_Connection=Yes;"

to

tdfCurrent.Connect = "ODBC;DRIVER={sql server};DATABASE=" & _
DatabaseName & ";SERVER=" & ServerName & _
"Uid=" & Username & ";" & _
"Pwd=" & Password

(plus, of course, change the routine to accept Username and Password being
passed to it)

 

[Kent Johnson]

Douglas,
Thanks!

This is what I have tried so far:
==================================
Dim db As Database, tdf As TableDef, rs As Recordset
StrUserID = "MyUser"
StrPwd = "MyPwd"

Set db = CurrentDb
gStrConnect = "ODBC;DRIVER={SQL
SERVER};SERVER=MyServer;Database=MyDb;Network=DBMSSOCN;UID=" & StrUserID &
";PWD=" & StrPwd

For Each tdf In db.TableDefs
If InStr(1, tdf.Connect, "MyDb") > 0 Then
With tdf
.Connect = gStrConnect
.RefreshLink
End With
End If
Next

SetConnectString = True
======================================
I only get:
"Login failed...Reason not trusted connection"
What can be wrong?

/Kent J.

 

[Douglas J. Steele]

Once the TableDef object has been added to the TableDefs collection, the
Connect property is read-only. You need to drop the table and create a new
one with the correct Connect property, as I do in the example to which I
pointed you.

 

[Kent Johnson]

OK!
Sorry but I can't get it to work.

I have copied everything from:
http://www.accessmvp.com/DJSteele/DSNLessLinks.html
except...

Sub FixConnections()
ServerName = "MyServer"
DatabaseName = "MyDb"
UserName = "MyUser"
Password = "MyPwd"

.....and...

Set tdfCurrent = dbCurrent.CreateTableDef(typNewTables(intLoop).TableName)
tdfCurrent.Connect = "ODBC;DRIVER={sql
server};DATABASE=" & _
DatabaseName & ";SERVER=" & ServerName & _
"Uid=" & UserName & ";" & _
"Pwd=" & Password
tdfCurrent.SourceTableName = typNewTables(intLoop).SourceTableName
dbCurrent.TableDefs.Append tdfCurrent

I still get "Login failed...Reason not trusted connection"

/Kent J.

 

[Douglas J. Steele]

You sure the username and password you're using work with that database and
server?

That connection string looks fine (unless Carl's got a mistake at
http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForSQLServer)

Unfortunately, I don't have any way to test at my end: we only use trusted
connection at work, and I don't have SQL Server installed here.

 

[Kent Johnson]

Yes, the usename and password is correct.

The script was almost correct exept the:
"Uid=" & UserName & ";" & _
should be....
";Uid=" & UserName & ";" & _

I have tested the "not trusted" script on:
1.) My local Win2003Server client with SQL2000-server installed and this
works fine. I guess it's because it's "trusted"
2.) The company network at my work with the production server "not trusted".
This will not work. First I get errormessage and then I'm promted to
login -"trusted connection" is checked.

/Kent J.