469,330 Members | 1,348 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,330 developers. It's quick & easy.

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

17
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.
Apr 14 '08 #1
1 1111
marcf
17
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
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.