Access Database just upsized and i'm going to kill someone!

[marcf]

Ello everyone,

Ok i've just upsized the database to SQL Server 2005, here is the code:

Set con = Application.CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic

sql = "INSERT INTO reminders (CompanyID, [Company Name], [Company Contact], Number, Appointtime, Appointdate, [User]) VALUES ('" & idnum.Value & "','" & [Company Name].Value & "','" & [Contact Name].Value & "','" & Telephone.Value & "','" & remtime & "', '" & Format([Recall Date].Value, "mm/dd/yyyy") & "','" & User & "')"

rs.Open sql, con, , , adCmdText

--------------------------------------------------
Which generates this query:


INSERT INTO reminders (CompanyID, [Company Name], [Company Contact], Number, Appointtime, Appointdate, [User]) VALUES ('35243','Some Company','Mr Smith','00000 - 000000','09:00:00', '03/26/2008','MRF')

If I use rs.open sql,con,,,adcmdtext it dies with Error in insert statement

If I open the Server manager and create a query using the above string it works fine!

Why?

Am I being stupid here?

Sorry quick edit here, the data is coming from Linked tables so this front end should already be connected fine.

[marcf]

I think I kinda sorted it.

Basically you can't assume that your DSN is working correctly, for some odd reason if you use application.currentproject.connection object it works fine unless you try to DELETE or INSERT.

Instead I swapped out the Con= Application.Currentproject.Connection for an SQL connection string (which I thought a machine DSN was supposed to do?)
now it all appears to work fine and aside from some jiggery pokery with dates and field names our database is about 75% faster.

If anyone can give me an idea why the DSN doesnt actually seem to provide a proper connection to an SQL Server i'd appreciate it as it seems pointless for it not to work properly.