ADODB not reading sql system_user?
Hi,
I am working on pulling the user information from a sql database to an Access 2007 front-end. I have had no problems pulling from any of the tables, but I can't seem to get it to read system_user - very important as Access no longer does it's own logins. I'll probably be doing a lot of this, but so far I am simply trying to get user login info onto a form. I have a table in SQL called Users and in Users is a field that corresponds to the SQL login. Here's what I have been trying to run:
Dim Uname As String
Dim Fullname As String
Dim cn As ADODB.Connection
Dim rsLOGINID As ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT u.Username as uname, u.FirstName + ' ' + u.LastName as fullname, FROM Users u WHERE u.Login = SYSTEM_USER"
Set rsLOGINID = New ADODB.Recordset
Set cn = Application.CurrentProject
rsLOGINID.Open strSQL, cn, adOpenStatic, adLockOptimistic
Fullname = (rsLOGINID("fullname"))
Me.txtUserLogin = Fullname
The select statement works fine when I run it as a query so there's no issue there. The above code works fine when I remove any reference to SYSTEM_USER. If I even just try to select SYSTEM_USER and remove the where statement, the recordset refuses to open. If I remove the Where and all else SYSTEM_USER, it runs, but obviously just gives me the first record it finds. Does ADO not read SYSTEM USER?
Any help would be much appreciated!
I am working on pulling the user information from a sql database to an Access 2007 front-end. I have had no problems pulling from any of the tables, but I can't seem to get it to read system_user - very important as Access no longer does it's own logins. I'll probably be doing a lot of this, but so far I am simply trying to get user login info onto a form. I have a table in SQL called Users and in Users is a field that corresponds to the SQL login. Here's what I have been trying to run:
Dim Uname As String
Dim Fullname As String
Dim cn As ADODB.Connection
Dim rsLOGINID As ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT u.Username as uname, u.FirstName + ' ' + u.LastName as fullname, FROM Users u WHERE u.Login = SYSTEM_USER"
Set rsLOGINID = New ADODB.Recordset
Set cn = Application.CurrentProject
rsLOGINID.Open strSQL, cn, adOpenStatic, adLockOptimistic
Fullname = (rsLOGINID("fullname"))
Me.txtUserLogin = Fullname
The select statement works fine when I run it as a query so there's no issue there. The above code works fine when I remove any reference to SYSTEM_USER. If I even just try to select SYSTEM_USER and remove the where statement, the recordset refuses to open. If I remove the Where and all else SYSTEM_USER, it runs, but obviously just gives me the first record it finds. Does ADO not read SYSTEM USER?
Any help would be much appreciated!
ASKER
Hi,
Thanks for the quick response, but now I'm not getting any result. I think it's now looking for a login name of 'system_user'. I have tried running a pass-through query on system_user and copying the result as the criteria for the ado code and it works, so the issue isn't that the login is incorrect in the database. Any other thoughts? Is there something special I need to do with the database connection or something?
thanks again,
Hassab
Thanks for the quick response, but now I'm not getting any result. I think it's now looking for a login name of 'system_user'. I have tried running a pass-through query on system_user and copying the result as the criteria for the ado code and it works, so the issue isn't that the login is incorrect in the database. Any other thoughts? Is there something special I need to do with the database connection or something?
thanks again,
Hassab
can you execute this on SQL and see what it returns:
SELECT u.Username as uname, u.FirstName + ' ' + u.LastName as fullname, FROM Users u WHERE u.Login = 'SYSTEM_USER'
ASKER
Ran it in query analyzer, got no results (there's an extra comma in there, removed it). ran it as a pass-through in access, no results. added a record to users called "system_user", ran in in the ado code and I got that record. grr. The weird thing is that I can run this as a passthrough (without the quotes) and get my record but if I do it in QA I get nothing. odd.
ASKER
Found the answer. well, sort of. I created a function to pull the sql user and used that function as the criteria:
Function GetSqlUser() As String
Dim qrd As DAO.QueryDef, rst As DAO.Recordset
Set qrd = CurrentDb.CreateQueryDef("
With qrd
.Connect = CurrentDb.TableDefs(0).Con
.SQL = "SELECT SYSTEM_USER"
.ReturnsRecords = True
End With
Set rst = qrd.OpenRecordset()
GetSqlUser = rst(0).Value
rst.Close
Set rst = Nothing
Set qrd = Nothing
End Function
then updated the sql string to:
strSQL = "SELECT u.Username as uname, u.FirstName + ' ' + u.LastName as fullname FROM Users u WHERE u.Login = GetSqlUser()"
Not sure why the ADO wasn't accepting system_user, but, whatever.
Thanks for your help!
-Hassab
Function GetSqlUser() As String
Dim qrd As DAO.QueryDef, rst As DAO.Recordset
Set qrd = CurrentDb.CreateQueryDef("
With qrd
.Connect = CurrentDb.TableDefs(0).Con
.SQL = "SELECT SYSTEM_USER"
.ReturnsRecords = True
End With
Set rst = qrd.OpenRecordset()
GetSqlUser = rst(0).Value
rst.Close
Set rst = Nothing
Set qrd = Nothing
End Function
then updated the sql string to:
strSQL = "SELECT u.Username as uname, u.FirstName + ' ' + u.LastName as fullname FROM Users u WHERE u.Login = GetSqlUser()"
Not sure why the ADO wasn't accepting system_user, but, whatever.
Thanks for your help!
-Hassab
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
this works
strSQL = "SELECT u.Username as uname, u.FirstName + ' ' + u.LastName as fullname, FROM Users u WHERE u.Login = 'SYSTEM_USER'"