472,133 Members | 1,167 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,133 software developers and data experts.

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

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!


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.
Apr 14 '08 #1
1 1214
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.
Apr 14 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Mark T. | last post: by
17 posts views Thread by Mell via AccessMonster.com | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.