User is null..?

[Kent Johnson]

Hi all,

I have this script which is supposed log me in to a SQL-server (not trusted)
from Access.

ServerName = "MyServer"
DatabaseName = "MyDb"
UserName = "MyUser
Password = "MyPwd"

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

....but Im promted by ODBC:
"Login failed for user (null). Reason: not associated with a trusted SQL
server connection"

What can be wrong?

/Kent J.

 

[Douglas J. Steele]

Is that your exact code?

 

[Kent Johnson]

Yes, it's a clip from the Sub.

Kent J.

Is that your exact code?

 

[Douglas J. Steele]

It can't be copied-and-pasted: there's no closing quote on UserName =
"MyUser.

BTW, do you have Option Explicit turned on?

 

[Kent Johnson]

Yes, you are right.
I erased the closing quote in the message when I changed the userid from the
real username.
I have Option Explicit turned off.

Maybe one solution is to set up a one-way-trust to my SQL-server.
But I guess that this must be done on the domain controller.

/Kent J.

 

[Douglas J. Steele]

You should always have Option Explicit turned on: it can help immensely.

Try turning it on and compiling your code. While it certainly looks as
though you're using the same variable in both places, perhaps it's not
seeing it as the same variable.

For that matter, where have you defined those values versus the rest of the
code?

 

[Kent Johnson]

OK!
I have now Option Explicit turned on.
Sub FixConnections()
Dim DatabaseName As String, Username As String, Password As String,
servername As String
servername = "Myserver"
DatabaseName = "MyDb"
Username = "MyUsername"
Password = "MyPwd"

But I have the same problem.

/Kent J.

 

[Douglas J. Steele]

I'm running out of ideas! <g>

What exactly is in your Connect property? Does it look correct? Based on
your values, it should be:

ODBC;DRIVER={sql
server};DATABASE=MyDb;SERVER=Myserver;Uid=MyUsername;Pwd=MyPwd

What happens if you create a pass-through query and paste that string into
the query as its Connect string?