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.Connection
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!