Create Login for new user - QueryDef

[Goondu]

I have a ADO code that work very well without issues. But when I tried to convert it to DAO, it does not work. Here is the code sample.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

strSQL = "CREATE LOGIN test WITH PASSWORD = xxx;"

Set db = CurrentDb
Set qdf = db.CreateQueryDef("")
qdf.Connect = "ODBC;DRIVER=SQL Native Client;SERVER=test\sqlexpress;DATABASE=myDB; " _
& "Trusted_Connection=No;Persist Security Info = True;UID=xxx;PWD=xxx"
qdf.ReturnsRecords = False
qdf.SQL = strSQL
qdf.Execute dbFailOnError

It's giving me an error "3146 - ODBC - call failed"

The connection string work fine in ADO and the SQL syntax is correct as well.

Anyone can see why it failed?

 

[gmmastros]

I haven't used DAO in a long time. However, if memory serves, there should be a way to set DAO to use a "passthrough" query. I encourage you to do a little research on this to see if it solves your problem.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Goondu]

Thanks for replying George,

I know I could use passthru. But that requires me to create a named query in Access.

I intention is to use VBA code only. Other Action queries, there are no problems but when using "CREATE LOGIN".

I'm looking for a way to code in VBA without the need of a named query.

 

[Goondu]

Ok,

After some checking and testing, the message was incorrect from Access.

Found out that the password needed the enclosed single quotation in order to get it to work. But it does not have problem with named query.