Few problems after upsized Access database to sql server (1 Viewer)

[dalmatian]

After upsizing access database to sql server I found a few problems.
My configuration is: Win xp, access 2010 (but I still using 2003 format mdb). SQL Server is Express 2008 R2 located on win server 2003 x64.
Connection is established through ODBC.
Upsizing tool didn't report any mistakes.
The first problem I found is with linked tables. In Access (before upsizing to sql) I created relationships between tables and works fine, but, after upsizing when I try to open linked table I received message: "The table or querry name 'dbo.tablenam' you entered in either the property sheet or macro is misspelled or refers to a table or querry that doesn't exist."
Relationships not working with sql server (gray button), and when I try to use Diagram, diagram also inform me: "The diagram cannot be opened because MS Access 2010 does not support database diagrams for the version of SQL Server to which your database is attached."
I started SQL server management studio on server and created diagrams. After that, nothing is changed, I receiving same error on linked tables.
I checked table properties - extended properties - MS_SubdatasheetName and correct table name is writen in Value field.
But, I noticed that the table name in error message is wrong. Table name must be 'dbo.tablename' not 'dbo.tablenam', and same situation is for all linked tables, the last letter is missing.
I have experimented with adding dot (or any other letter) to the end of name of subdatasheet in Value field, and after that, linked table working perfectly.
This solution for me is temporarily satisfactory, but I'm not sure that is correct way for solving this problem.
Any ideas?

Other problems is in vba code.
I'm using form to display record about our computers on lan, and on that form
I put command button which opened datasheet with some related information (local users) for that particular computer.
That code works fine in access (before sql), but now after pressing command button I receiving: " Run-time error '102': Incorrect syntax near ';'. ".
My code is:

Private Sub OpenLocalUsers_Click()
Dim sWHERE As String
sWHERE = "[Computer]= '" & Me.Computer & "'"
DoCmd.OpenForm "FLocalUsers", acFormDS, , sWHERE
End Sub

Pressing Debug button lead me on
DoCmd.OpenForm "FLocalUsers", acFormDS, , sWHERE
row. I remove ", , sWHERE", and after that not receiving any error message, but instead of related records I see whole Localusers table.
I'm not good in vba proggraming, anyone can help?

 

[bparkinson]

This is all quite vexing. One bit of low hanging fruit I can think of is this:

Change this code to look like this, and then post what sWhere is literally:

Private Sub OpenLocalUsers_Click()
Dim sWHERE As String
sWHERE = "[Computer]= '" & Me.Computer & "'"
msgbox sWhere ''<---------
DoCmd.OpenForm "FLocalUsers", acFormDS, , sWHERE
End Sub

As to diagrams and relationships, on linked tables you no longer have the ability to edit them from Access. If you define relationships and allow SQL Server to enforce referential integrity, it will. If your Access front end violates that referential integrity, it will throw an error generated from SQL Server. Also, required fields in SQL Server will behave similarly, a Good Thing.

All of your database design activities now must happen in SSMS. You also now have tremendous opportunities remove code that uses local recordsets, replacing them with views, stored procedures, and triggers.

 

[dalmatian]

Thanks for replay, but I still get a same message:
Run-time error '102': Incorrect syntax near ';'.
and debug pointed me at same line:
DoCmd.OpenForm "FLocalUsers", acFormDS, , sWHERE

 

[dalmatian]

Starts over again.
In access database (without SQL Server as BE), I was erased all relationships between tables, and also in each table properties under 'subdatasheet name' field I chose [None]. Also, I deleted all queries and compact and repair database.
After that, I started sql server upsized tool and upsized all tables on sql server.
Then, on sql server I created database diagrams (using SSMS).
After all these steps are linked tables to work properly in access front end (adp file extension).
But, I have several tables that are associated with more than one table (with 'one to many' connection).
In that case, subdatasheet name field must be filled with only one table (not [Auto]). Access for the first use of that table asks which tables to link, and tables will appear correctly.
But the next attempt to see the same linked table, I get the message:
"The table or querry name 'dbo.tablenam' you entered in either the property sheet or macro is misspelled or refers to a table or querry that doesn't exist."
The same solution as before still working, adding any letters at the end of the field
'subdatasheet name' display properly linked tables (in access front end or SSMS).
But is this the right solution?

 

[dalmatian]

I experimented a bit and I found a solution for VBA code from first post.
To repeat, in Access (without SQL BE) VBA code to filter the child forms that starts from the master form is:
Private Sub OpenLocalUsers_Click()
Dim sWHERE As String
sWHERE = "[Computer]= '" & Me.Computer & "'"
DoCmd.OpenForm "FLocalUsers", acFormDS, , sWHERE
End Sub
Main form displayed all relevant informations about one computer. Command button opens a subform in this case, the defined local users.
But the same code does not work properly with SQL BE.
All the time I get a message:
Run-time error '102': Incorrect syntax near ';'.

Code that I've found that works with SQL Server is:
Private Sub OpenLocalUsers_Click()
DoCmd.OpenForm "FLocalUsers", acFormDS
DoCmd.ApplyFilter , "[Computer]= '" & Me.Computer & "'"
End Sub