I'm using OleDb to connect with an Access Database. I have anywhere from
10 to over 100 records that I need to either INSERT if the PK doesn't
exist or UPDATE if the PK does exist, all in a single transaction. Does
anyone have an SQL statement I can throw at it that would accomplish
this?
If I can't figure out how to do it, I'm going to have to send two
discreet SQL commands for each record which will take infinitely longer
than a single transaction.
*** Sent via Developersdex http://www.developersdex.com ***
Dec 24 '07
13  8881 
Terry Olsen <to******@hotma il.comwrote in
news:47******** *************** @news.qwest.net :
There's about 20 people in our work group that will be updating
the data. They are all IT Technicians and the database keeps all
the PC Information (we have around 2000 PC's in our district). The
database is often used because in addition to the PC audit info,
it's also used for our "Netop Phonebook" and disaster recovery
information (where is the current ghost image located for each
pc?). It's also used for our WakeOnLan requirements. So it's a
pretty busy database. Shame we had to lose the SQL db.
With so many functions in one MDB, it sounds to me like you might be
wise to seperate out the different functions into different back
ends, so that should one of them be corrupted, it won't take down
the others.
Of course, that may not be worth the effort if the different
functions are interdependent (and have RI defined between them). But
any complete sets of tables that are related but no related to
others would be a good candidate for pulling out into a different
MDB back end.
Just a thought.
Seems to me that you really ought to go to your direct supervisor
and work up the management chain to get authorization to use SQL
Server.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
"Tony Toews [MVP]" <tt****@teluspl anet.netwrote in
news:96******** *************** *********@4ax.c om:
But in a 100 mbps LAN performance is just fine.
It's just fine in 10Mbps, if you know what you're doing. My first 3
professional Access apps (Access 2) were all running on 10BaseT
networks, and they were just fine.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Yup, we're talking about a WAN situation here, a busy WAN and each
protocol is throttled. So it can take a while to open an Access file.
We have a few Access applications that IE put out for the different
locations to use. They have an MDB file in a shared directory somewhere
on the network. We get a lot of calls on those. "I keep trying to open
this file and it freezes up." They wouldn't give it time to open and
were ctr-alt-deleting it. I found it took anywhere from 5-10 minutes to
open.
This I was trying to avoid by writing a FE in VB and using OleDb
Transactions to update the file. But I understand now about breaking it
up into a BE and FE, using Access for both. Now to bone up on my Form &
VBA skills.
*** Sent via Developersdex http://www.developersdex.com ***
Terry Olsen <to******@hotma il.comwrote:
>We have a few Access applications that IE put out for the different
locations to use. They have an MDB file in a shared directory somewhere
on the network. We get a lot of calls on those. "I keep trying to open
this file and it freezes up." They wouldn't give it time to open and
were ctr-alt-deleting it. I found it took anywhere from 5-10 minutes to
open.
This I was trying to avoid by writing a FE in VB and using OleDb
Transactions to update the file. But I understand now about breaking it
up into a BE and FE, using Access for both. Now to bone up on my Form &
VBA skills.
Ah, a WAN is a big problem. Especially a busy WAN. And you've encountered some of
the problems.. You've also been lucky in that the database didn't corruption. Your
best options are Terminal Server or SQL Server.
You might want to go back and try the OleDB etc solutions but I'm not very hopeful.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Philip Boonzaaier |
last post by:
I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in the
selected table- If it exists, then the colums must be UPDATED, if not, they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values...
|
by: Shapper |
last post by:
Hello,
I have created 3 functions to insert, update and delete an Access
database record. The Insert and the Delete code are working fine.
The update is not. I checked and my database has all the necessary
records in it when testing it. I get the error "No value given for one
or more required parameters." when I try to update the database.
Can you tell me what am I doing wrong?
|
by: DraguVaso |
last post by:
Hi,
I'm writing a VB.NET application who has to insert/update and delete a whole
bunch of records from a File into a Sql Server Database. But I want to be
able to knwo the 'result' of my ctions.
for exemple:
- after an INSERT: knowing if this happened well or not
- after an UPDATE: knowing wich number of records were updated (or if there
were records udpated or not)
|
by: erin.sebastian |
last post by:
Hi all,
I have a really silly problem that i can't find the answer too. I am
working with VB.NET and i am trying to insert a new record into my
access database (pretty easy right?) well i am getting a syntax error
in my SQL statement and nothing i do will correct it. It looks FINE to
me, can anyone help me out??
Thanks so much in advance, here is the code i am using
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
|
by: Tom Allison |
last post by:
I seemed to remember being able to do this but I can't find the docs.
Can I run a sql query to insert new or update existing rows in one query?
Otherwise I have to run a select query to see if it's there and then
another one to update/insert.
What I'm trying to do is create a counter for each key, insert a value
of 1 or increment the value by 1 and then set another specific row
(where key = $key) to always increment by 1.
| | |
by: Darin |
last post by:
Since there is no 64-bit Jet (MS Access) OLEDB driver, what is the
recommended solution for this delimma.
Our application uses SQL Server as the daatabase engine, but we have
about 5 meg of static data stored in an Access database. We chose that
because it is easy to read, and can be password protected.
Since Microsoft doesn't have a 64-bit OLD to Access, the application has
to be set to run in 100% 32-bit mode to get it to run. This...
|
by: cooperkuo |
last post by:
Dear all,
I have a question about ADO in the subform. I know how to use ADO to insert/update/select data into the sigin form, but wehn I try to do it in the form with subform((Datasheet). I don't have any idea how to insert/update/select the recordset data into the subform(Datasheet), especially insert/update. Would someone can give me a idea or sample code to me? Appreciate your help.
OS: windows XP +SP2+ Access 2003+SP2
Database: MS...
|
by: kumardharanik |
last post by:
Hi Friends, The below code is a sample code for insert, update and delete using datagrid but i need to convert the entire code for datagridview.. Plsss help me..
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
|
by: ndhvu |
last post by:
Tables: Buy_Header and Buy_Detail.
- Buy_Header: info. of each buy (buy_id(PK, auto number), date, shop, bought_by, ...)
- Buy_Detail: info. of each item from each buy (buy_detail_id(PK, auto number), buy_id, item_name, price, quantity,...)
Target:
- Make a main-form of table with sub-form of table to insert/update records for both tables. Each form of each and the sub-form can have many records of .
- When update, you can add more...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
| | |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |
