New SQL Version errors

[Guest]

I have an Access frontend linked to a SQL tables.

We are running a Windows NT 4.0 Server.

Recently updated from SQL 7 sp 3 to SQL 2000 sp3.

I'm having problems with one form/table. The form takes Order ond Lot
information entered by the EU and places it into a SQL table using the
DoCmd.RunSQL command. This worked prior to the SQL Upgarde and works for
other forms/tables were I do similar entries. The only difference is the
table with the problem uses an Autonumber key field.

Data can be manually entered, but not entered with a query.

Also, when I run the front-end from a system with Win NT operating system it
works fine, but with WIn XP it doesn't respond.

I have tried changing the syntacs (sp?) of my SQL statment. The permissions
have been verified.

I'm in over my head and any suggestions would be welcomed.

 

[Douglas J Steele]

When you say there's an Autonumber field, do you mean in Access, or are you
talking about an Identity field in SQL Server?

Is there a Primary Key in SQL Server?

If there is, what's the SQL you're trying to run (and what's the name of the
Identity field in SQL Server)?

 

[Guest]

It is a SQL table so it's an Identity field in SQL that is the primary field
for the table.
I'm not trying to enter data into this field. I just need to generate a new
record. I've tried both queries:

"INSERT INTO tblCMainSpool ( CLotId, ShopOrder, CSDate )
SELECT [Forms]![frmFtrCableLotNew]![txtLot] AS Expr1,
[Forms]![frmFtrCableLotNew]![txtShopOrder] AS Expr2, Now() AS Expr3;"

"INSERT INTO tblCMainSpool ( CLotId, ShopOrder, CSDate )
VALUES('" & Forms![frmFtrCableLotNew]![txtLot] & "', " &
Forms![frmFtrCableLotNew]![txtShopOrder] & ", #" & sdVar & "#)"

I also thought the date format might be a problem so I removed the date from
the above queries with the same results. I do not get an error, the system
is non-responsive until the odbc times out.

Thanks for your assistance.

 

[Douglas J Steele]

How are you running the SQL? If you're using

DoCmd.RunSQL strSQL

try:

CurrentDb().Execute strSQL, dbFailOnError

and put in error trapping. Maybe that will shed some light on the problem.

I'm assuming tblCMainSpool is an attached table.

Another option might be to create a pass-through query, update its SQL
property each time and execute the query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

It is a SQL table so it's an Identity field in SQL that is the primary field
for the table.
I'm not trying to enter data into this field. I just need to generate a new
record. I've tried both queries:

"INSERT INTO tblCMainSpool ( CLotId, ShopOrder, CSDate )
SELECT [Forms]![frmFtrCableLotNew]![txtLot] AS Expr1,
[Forms]![frmFtrCableLotNew]![txtShopOrder] AS Expr2, Now() AS Expr3;"

"INSERT INTO tblCMainSpool ( CLotId, ShopOrder, CSDate )
VALUES('" & Forms![frmFtrCableLotNew]![txtLot] & "', " &
Forms![frmFtrCableLotNew]![txtShopOrder] & ", #" & sdVar & "#)"

I also thought the date format might be a problem so I removed the date from
the above queries with the same results. I do not get an error, the system
is non-responsive until the odbc times out.

Thanks for your assistance.

When you say there's an Autonumber field, do you mean in Access, or are you
talking about an Identity field in SQL Server?

Is there a Primary Key in SQL Server?

If there is, what's the SQL you're trying to run (and what's the name of the
Identity field in SQL Server)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


system
it