I am a web developer who works plenty with SQL Server, and I really know very little about Access. But I have a project now that requires me to know something about it so I've been doing quite a bit of research.
Briefly: A definitive data set is kept in SQL server. An important user needs a copy of this data in her local copy of Access. Currently an antiquated process involving email and human intervention keeps this up to date (you don't want to know!) but that's becoming unfeasible very quickly. So my research on these forums and elsewhere has turned up a couple options and I need a qualified opinion to help me choose the best one, or suggest one I haven't even thought of. One caveat: I can't replace all of the user's access data indiscriminatel y because there are columns in Access not in SQL Server that she manages manually.
1) My first inclination: export updated SQL records once a day to an Excel file. That's easy. Then create a macro in Access to overwrite the affected records with new data.
-OR-
2) Do something with linked tables from SQL Server? I worry about overwriting the user's manually entered data with this option, but am still learning about it. Also, I don't want Access to be able to change data in SQL, but I think I can control this with user permissions, no?
Your opinions are welcome. Many thanks in advance.
4  1358   ADezii 8,834
Recognized Expert Expert
I am a web developer who works plenty with SQL Server, and I really know very little about Access. But I have a project now that requires me to know something about it so I've been doing quite a bit of research.
Briefly: A definitive data set is kept in SQL server. An important user needs a copy of this data in her local copy of Access. Currently an antiquated process involving email and human intervention keeps this up to date (you don't want to know!) but that's becoming unfeasible very quickly. So my research on these forums and elsewhere has turned up a couple options and I need a qualified opinion to help me choose the best one, or suggest one I haven't even thought of. One caveat: I can't replace all of the user's access data indiscriminatel y because there are columns in Access not in SQL Server that she manages manually.
1) My first inclination: export updated SQL records once a day to an Excel file. That's easy. Then create a macro in Access to overwrite the affected records with new data.
-OR-
2) Do something with linked tables from SQL Server? I worry about overwriting the user's manually entered data with this option, but am still learning about it. Also, I don't want Access to be able to change data in SQL, but I think I can control this with user permissions, no?
Your opinions are welcome. Many thanks in advance.
How about DTS (Data Transformation Services)? You can even create a DTS Package to periodically Transform the Data to a specific Access Database from SQL Server then run Action Queries within Access to Update/Delete/ Append, etc, the appropriate data. Your two Data Sources will be completely independent, you will not have to worry about Permissions, and the User cannot inadvertantly change Data within SQL Server. Since this important User needs just a 'Copy' of the Data within her Access Database, this may be a viable solution.
How about DTS (Data Transformation Services)?
Thanks a lot for that great idea. I think I was getting hung up with the idea that the solution lay in Access itself, but I'm going to look more into DTS now and report back.
Other ideas or comments are naturally still welcome.
Is there a Primary Key for the data table?
Does the Access user maintain their data via a form currently?
The SQL table could be a linked table (user with READ rights). Displayed in a form. The user could edit and maintain his/her portion of the information in another table via a subform. No need for updates or worrying about changing each other's data. This also leaves you with a smaller mdb file if your database is continually increasing in size.
Hi
I think you need to create a DSN name which connects access to sql
In access, then try to export the link tables which is the orginal tables in sql table..Then you can also create a local tables in access itself for your user input and every thing related with access.By this method ,you can get the latest values in sql server always available in access
Regards
Dilip
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Lorenzo Bolognini |
last post by:
Hi all,
i am an ASP developer with 1,5+ years experience. After sneaking around the
ASP Classic world for a bit i became a bit dissatisfied of my ASP code
practices so i'd like to have some advice from the advanced/pro users...
some advice that would help me to grow up!
I know that many (if not all) the stuff that was a hassle in ASP 3 has been
corrected in ASP.NET/VS.NET (which i'm studying) but ASP 3 will still be
around for a long...
|
by: great_googley_moogley |
last post by:
Greetings,
I am in the process of installing a SQL database at a customer
location. I have determined that there are 3 ways to do this, and I
wanted to know which is the best of the 3.
1 Install From Script.
In this method I create the database and its objects in scripts that
are run via osql utility on the SQL server machine. For loading any
initial data that I need in the database I also run bcp commands.
|
by: DrUg13 |
last post by:
In java, this seems so easy. You need a new object
Object test = new Object() gives me exactly what I want.
could someone please help me understand the different ways to do the
same thing in C++. I find my self sometimes, trying
Object app = Object();
Object *app = Object();
Object app = new Object();
|
by: Alex |
last post by:
Hi all,
I'm looking for some advice on how best to implement storage of access
logs into a db/2 8.1.4 database running on a RH 7.2 system.
I have 5 (squid) web caches running here that service the whole
university. All access to external web sites must go through these
caches. Each cache generates a gzip'd access log file that's about
100Mbytes every night.
|
by: Greg |
last post by:
I'm fairly new to access (using 2002) and am setting up a DB for work.
along with each record the user also needs to make a flow diagram
(previously, these reports were composed in word and they used the
autoshapes to create this diagram). I was wondering what the best way
to incorporate this into the DB. I have read about how saving them as
pictures in the DB bloats the size, which is unacceptable, but it
needs to be easy to do (not...
| | |
by: BK |
last post by:
Our .Net team has just inherited a junior programmer that we need to
get up to speed as quickly as possible. Unfortunately, his skill set
is largely Access with some VB6 and ASP classic experience. We employ
some parts of XP such as pair programming, and this should help. Other
than books, does anyone have any suggestions? His skill set is pretty
antiquated and we need to get him up to speed as quickly as possible so
any suggestions...
|
by: Yehia A.Salam |
last post by:
Hello,
I am building an ASP.net website that connects to an XML database, and was
wondering which is the best way to create the connection if I need frequent
access to the database, I have one of the three options:
1. Use one instance of XmlDocument thought the whole Application by
initializing the class in Global.asax in the Application_Start Event.
2. Use one instance per session using the Session_Start Event.
3. Create and...
|
by: =?Utf-8?B?SlA=?= |
last post by:
I have a web application that will handle anywhere for 50 to 175 users at one
time but will likely have a user base in the 100s. This application contains
a GUI interface for the every day user and web services that can access the
GUI modules for 3rd party applications needing to get shared data
In the initial design plan, the idea was presented to retrieve the data for
the user’s dashboard and hold that data in session, only connecting...
|
by: trullock |
last post by:
Hi,
Can anyone suggest the best way to go about the following...
I'm tracking clicks (mouse down x,y coordinates) on a web page by
using some javascript to create an XHR which sends the coordinates to
a recording service, such as:
/Record.ashx?X=123&Y=456
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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: 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: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
