473,387 Members | 1,700 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 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 1282
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

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

Similar topics

by: Bob Davies | last post by:
I have upsized an Access database into SQL Server and manged to get the data in place ok. The wizard created an Access Project which I have started to modify. However, I am also trying to get...
by: ImraneA | last post by:
Hi there I had pleasure of upsizing Access v97 db to Access v2K/SQL 2K. Wish to provide some knowledge gained back to community - hopefully help others. 1.Using Upsizing wizard - will...
by: Jose Fernandez | last post by:
Dear Friends, A friend of mine have asked me a favor, he has started a new small bussines to rent equipment, he wish to control the inventory and to issue renting orders from the clients, but it...
by: Mark T. | last post by:
Hi all, I would like to find out about a Microsoft Access database that was "converted" to SQL. Not sure what that means, and would appreciate anyone letting me know. I'm at the intermediate...
by: David C. Barber | last post by:
upsized an MDB to ADP/SQL Server 2000 under Access 2000. All the DAO code that I've changed to ADO code is working fine, HOWEVER the form Record Source itself does not seem willing to return data....
by: deko | last post by:
Do I get more scalability if I split my database? The way I calculate things now, I'll be lucky to get 100,000 records in my Access 2003 mdb. Here some math: Max mdb/mde size = 2000 x 1024 =...
by: Mike | last post by:
I have an Access DB that I upsized to a SQL server DB. The tables that I upsized I can't seem to modify. I wanted to insert some data into the table and I am getting the following error: ...
by: George H. Slamowitz | last post by:
Hello All I am trying to convert a MS Access 2000 application to a VB application (Just Started Yesterday) I am using Visual Studio .NET 2003 utilizing Visual Basic .NET I think I have a...
by: Mell via AccessMonster.com | last post by:
Is there a way to find out where an application was created from? i.e. - work or home i.e. - if application sits on a (work) server/network, the IT people know the application is sitting...
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

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.