error message

Question

error message

Stephen Craig

SSC-Addicted

Points: 464
More actions
June 1, 2006 at 8:50 am

#91117

Hi,
I am trying to upgrade an access database to SQL server but am completely new to SQL server.
I have used the DTS to import tables from access and have created an ADP database.
When I open one of the forms (frmProject) I get an error message that says 'syntax error or access violation'.
This form contains 2 subforms (frmProjectRolesub and frmProjectEmployeesub).
The first subform is linked to the main form (many to one) and the second subform is linked to the first subform (many to one).
The error message appears to be linked to the 2nd subform as when I remove link between the first & second subforms the error disappears (but obviously this isn't a solution as I need the link!)
Has anyone come across this before - is it not possible to have 2 linked subforms on a form in an ADP database?
any help would be appreciated.
thanks
Steve

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

The SQL used for Access (Jet-SQL) is not the same as the SQL used for SQL Server (Transact SQL). They are similar but different. The differences might be causing your problems. Do you use IIF? SQL Server uses CASE. Do you use NOW() for the current time? SQL Server uses GETDATE().

You need to look at your code to see if that's the problem.

-SQLBill

Marshall Smith SSCrazy Points: 2041 More actions · Answer 2

I'm more or less with SQLBill on this one. The syntax differences between Access and SQL Server are pretty huge. Tracking down a syntax error when you first upsize the database can be tedious.

Test #1: Will the 2nd subform open and load by itself, outside the main form? If not, will the datasource resolve outside the form (i.e., can you open the view or table directly)?

Test #2: Do all of your forms have views or tables as their datasource? Forms with a stored procedure as their datasource won't sync in the same way.

Test #3: Have you double-checked the values of your Master and Child fields? Have you checked the value of the ResyncCommand property on each form?

Test #4: Have you attempted to replicate the problem with a simpler set-up? Different views? Just using the wizards?

It is, in fact, possible to have multiple linked subforms in an ADP. However, subforms tend to be a little crankier than they are in an MDB. It takes some trial and error to work out all the bugs.

I have an excellent book on working with ADP's (there are precious few). I'll try to remember to get the title and author to you tonight.

Stephen Craig SSC-Addicted Points: 464 More actions · Answer 3

Thanks for your replies -

If you could get that book title to me I would really appreciate it - thanks

Steve