Problems after linking tables from SQL to Access 2007

[rwahdan@gmail.com]

Hi,

After linking the table to access 2007, problems with my forms started! example code:

Private Sub Form_Load()


MsgBox theactive


Set dbs = CurrentDb


strSQL = "SELECT * FROM dbo_users_login where isactive = " & theactive & ""


Set rsSQL = dbs.OpenRecordset(strSQL)


MsgBox rsSQL.RecordCount


logintxt.Caption = rsSQL.Fields![FullName]


lbl60.Caption = "Logged in as      " & rsSQL.Fields![usermenu]


If theactive = 0 Then


test = MsgBox("You can view the menu after login", vbExclamation)
DoCmd.Close acForm, "mainmenu", acSaveYes


Else




If rsSQL.Fields![usermenu] = "Admin" Then


NavigationButton11.Enabled = True


Else


NavigationButton11.Enabled = False


End If




Set dbs1 = CurrentDb


strSQL1 = "SELECT * FROM logfile"


Set rssql1 = dbs.OpenRecordset(strSQL1)


rssql1.AddNew


rssql1.Fields![FullName] = rsSQL.Fields![FullName]
rssql1.Fields![UserName] = rsSQL.Fields![UserName]
rssql1.Fields![thedate] = Date
rssql1.Fields![login_time] = Time


rssql1.Update


End If


End Sub

this code was working fine when the tables were in MS Access and created with MS Access. after moving the tables to SQL server 2008 r2 express then deleting the tables from Access 2007. I linked the table from SQL server 2008 r2 express to Access 2007 leaving the forms (from before). when trying the login form i did succeeded after only change the table name from "users_login" in the old database to "dbo_users_login" in the new linked table.



the main menu form though, i tried changing the table name (same table) to the same i did above from "users_login" in the old database to "dbo_users_login" in the new linked table but it tells me no records!

please help about this. is there an other way to deal with linked tables!

thanks,
Rami

[ItsMe]

Maybe it would be easier to link tables to SQL and then use the name of linked tables in your code, queries, and forms.

Then, when you have the need to create a pass-through query, you can do just that. With pass-through queries you can use T-SQL specific SQL statements and talk directly to SQL server.

[rwahdan@gmail.com]

Thanks for the reply,

If i link tables to SQL then use access as the coding end then what is the use of linking to SQL? Sorry to ask, I am new and all what i wanted (by linking from SQL to Access, not to get people access my code and designs when it is live), i heard having the tables in SQL will be secured, that is the reason. Could you please explain about you told me linking from access to sql and use access forms and codes!

thanks

[ItsMe]

How did you connect to the database on SQL server? When you look in the navigation pane do you see any Globe Icons next to the name of table objects or query objects. If not, it would seem you are making a connection at the Application level, from your Access file to the SQL Server Database. The code you posted is not exactly clear how you are connecting.