New SQL Version errors

J

Justin

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.
 
D

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

Justin

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.
 
D

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!)


Justin said:
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 said:
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
Click to expand...
Click to expand...
 
Top