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?
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?