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.Cur rentProject.Con nection
Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimisti c

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,,,adcmd text 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.cur rentproject.con nection object it works fine unless you try to DELETE or INSERT.

Instead I swapped out the Con= Application.Cur rentproject.Con nection 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.