f



Error Writing to MS Access Database with Replication

Greetings,

I have a Microsoft Access database that is replicated accross several
computers using the built-in Access replication technology.  Access
replication supports automatically synchronizing updates that have
been applied separately to each copy of the database.  When
replication is enabled in MS Access, a number of "system" columns are
added to every table in the database in order to facilitate the
synchronization database.  Note that these columns are not visible by
default within MS Access.

I would like to insert new rows into one of the tables in this
database using PROC SQL.  The statements that I am using to perform
this function worked fine before replication was enabled on the
database.  After replication was enabled, I now receive an error which
indicates that SAS is attempting to modify the contents of the
replication system columns.  SAS should NOT be doing this, as the
contents of these columns are automatically maintained by MS Access,
and are protected against modification by external applications.

The code that I am using, and the error message that I receive are
provided below.  Can anyone advise of a simple solution to this
problem?  Perhaps some other option to the libname statement or
connect string that I have overlooked...?

Thanks,
s/KAM


508  %let MJFile=\\Speedy\D\Program Files\MJ Systems
\LogSleuth2\Database\LogSleuth.mdb;

<snip>

525  libname MJ ODBC noprompt="uid=admin;pwd=&MJPW;dsn=MS Access
Database;DBQ=&MJFile;"

526                  dbindex=yes reread_exposure=yes;
NOTE: Libref MJ was successfully assigned as follows:
      Engine:        ODBC
      Physical Name:

<snip>

600  proc sql;
601     /* create new cross-section data in MJ format */
602     create table new_PROJECT_SECTION as
603        select &PROJECT_ID as PROJECT_ID,
604               "&XSName" as SECTION_NAME,
605               MJW_ID,
606               LOG_ID,
607               logNumber as LOG_INSTANCE,
608               0 as SCROLL_POS
609        from XS_logs
610        order by PROJECT_ID, SECTION_NAME, LOG_INSTANCE;
NOTE: Table WORK.NEW_PROJECT_SECTION created, with 41 rows and 6
columns.

611     /* remove any existing xsection from MJ under same name */
612     delete from MJ.MJ_PROJECT_SECTION where
SECTION_NAME=upcase("&XSName");

NOTE: 275 rows were deleted from MJ.MJ_PROJECT_SECTION.

613     /* add new cross-section */
614     insert into MJ.MJ_PROJECT_SECTION
615        (PROJECT_ID, SECTION_NAME, MJW_ID, LOG_ID, LOG_INSTANCE,
SCROLL_POS)

616        select PROJECT_ID, SECTION_NAME, MJW_ID, LOG_ID,
LOG_INSTANCE, SCROLL_POS

617        from new_PROJECT_SECTION;
ERROR: CLI execute error: [Microsoft][ODBC Microsoft Access Driver]
You cannot modify the replication system object 's_Lineage'.
ERROR: ROLLBACK issued due to errors for data set
MJ.MJ_PROJECT_SECTION.DATA.
618     quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used:
      real time           0:01.21
      cpu time            0:00.71

619

0
KevinMyers (190)
3/4/2007 10:23:37 PM
comp.soft-sys.sas 142828 articles. 3 followers. Post Follow

1 Replies
1225 Views

Similar Articles

[PageSpeed] 40

On Mar 4, 4:23 pm, KevinMy...@austin.rr.com wrote:
<snip>
> Can anyone advise of a simple solution to this problem?  
> Perhaps some other option to the libname statement or
> connect string that I have overlooked...?
>
<snip>

Looks like I get to answer my own question.  By switching the LIBNAME
statement to connect to my MS Access database via OLEDB rather than
ODBC, everything worked as desired.  BTW, this was SAS v8.2 under
Windows 2000 Pro.

s/KAM


0
KevinMyers (190)
3/5/2007 12:22:12 AM
Reply:

Similar Artilces:

MS Access Database Replication from VB
Hello, Have any of you ever controlled MS Access Replication from a VB application? I have a VB6 application where I have to send copies of the underlying MS Access 2000 database in remote areas (no LAN or Internet connections), then get the databases back and synchronize them. Any suggestions, articles, etc. would be greatly appreciated. Bie I have done code in Access which replicates six copies of a database together as an automated process. The same method calls can be run in VB as well. (It is Access 97, but I don't think that makes a difference, s long as the versions of ADO, ...

Error writing to SQL database from Access
Greetings, I am developing an application that writes values from an Access 2000 database to an SQL 7.0 server using DAO and linked tables. It seems to work fine for the first 26 of 108 tables, but part way through the population of the 27th table it stops and displays a 'Runtime Error 3001 Invalid Arguement' message. Not sure what to make of this. Any help would be appreciated. Daryl For nZone = 1 To p_intZoneCount If p_bRunZones(nZone) Then Debug.Print "Writing zone " & nZone & "..." With p_zneData(nZone) ...

databases.ms-access ....soft download
Extreme Web Reports 2005 - Soft30.com The wizard scans the specified MS Access database and records information such as report names, parameters and subqueries. ... www.soft30.com/download-1-11975.htm - 31k - Cached - Similar pages MDBSecure 1.0.8.0 - Soft30.com Utility which makes it easy to create secure MS Access Databases, ... MS Access 2000/2003 format. 30 day money back guarantee, 30 day trial. ... www.soft30.com/download-134-3249.htm - 32k - Cached - Similar pages Access Password Recovery Master 1.0 Screenshot - Soft30.com System Requirements: All versions up to MS Access 2003 are sup...

MS Access Database Replication from VB
Hello, Have any of you ever controlled MS Access Replication from a VB application? I have a VB6 application where I have to send copies of the underlying MS Access 2000 database in remote areas (no LAN or Internet connections), then get the databases back and synchronize them. Any suggestions, articles, etc. would be greatly appreciated. Bie ...

Create MS/ACCESS database app for non-ms/access users
Is it possible to create an MS/ACCESS database application and package it for users who do not have MS/ACCESS loaded in their PCs? I was told that this is possible, but I don't know what software is needed. Do you know? If so, please answer this post. Thanks. SueB *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Per Sue Bricker: >Is it possible to create an MS/ACCESS database application and package >it for users who do not have MS/ACCESS loaded in their PCs? > >I was told that this is possible, but...

Need for speed: writing to a database with SAS/ACCESS
Occasionally, I have to transport a SAS dataset into a (local, single-user) MySQL database, and have used MySQL and ODBC engines of SAS/ACCESS for this task. In both cases, and regardless of whether I transfer data with something like 'data db.table; set sas.dataset; run;', or with PROC COPY, I have found writes to be *very* slow - a singe numeric column with 100,000 rows might well take 10 minutes to pass to MySQL. I was wondering if there are ways to improve the write speeds, and would appreciate a suggestion or a helpful link. Thank you. Have you tried playing w/the DBCOMMIT optio...

Replicated Application MS Access database
A client has a split database. However, the frontend is replicated. Since both frontend databases(replicated + DM)are both on same network drive, we would like to remove the replication of the front end. Can we just delete one of them and point the shortcut to open the other? *** Sent via Developersdex http://www.developersdex.com *** Eric L <bopamax@yahoo.com> wrote in news:48da67f8$0$89386$815e3792@news.qwest.net: > A client has a split database. However, the frontend is > replicated. Since both frontend databases(replicated + DM)are both > on same network drive, we would ...

help MS ACCESS\SAS, general database questions
Hi to all, I need some help deciding on the best database system for our specific application. OPTIONAL INFO: We currently have 2 separate projects that need to be updated from dos-based dBase. I'll only describe one of these but the other is comparable in size. The project currently has about 32 files. The 2 largest are about 30,000 records and 5 columns include data from the last 30 years. The other 30 files are created by year and are about 1000 records and 10 columns. There are only 2 or 3 people keying in data, never at the same time. We also have a sonic digitizer (measures...

Re: Need for speed: writing to a database with SAS/ACCESS
Have you tried playing w/the DBCOMMIT option on the libname statement? Try bumping that up to say, 10k & see if it makes a difference. Other options to play with available here: http://support.sas.com/onlinedoc/913/getDoc/en/acreldb.hlp/a002915222.ht m HTH, -Roy -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Dimitri Shvorob Sent: Wednesday, December 20, 2006 10:53 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Need for speed: writing to a database with SAS/ACCESS Occasionally, I have to transport a SAS dataset into a (local, single-user) MySQL...

Mainframe SAS to PC located MS ACCESS database
Dear SAS-L members, Is there a "SAS" or "other" set of products that would allow me to run a batch job in MVS/OS390 SAS, connect through the network, and actually read an MS ACCESS database located on a PC or FTP site? I have been searching the SAS-L archives but did not find a recent discussion on the options to accomplish this connection. I know it has not been possible in older versions of SAS and its connection products, but maybe there is something new I am not aware of. It would not have to be an "ALL SAS" solution if there would be some mainframe softwa...

help SAS/MS ACCESS, general database questions
Hi to all, I need some help deciding on the best database system for our specific application. OPTIONAL INFO: We currently have 2 separate projects that need to be updated from dos-based dBase. I'll only describe one of these but the other is comparable in size. The project currently has about 32 files. The 2 largest are about 30,000 records and 5 columns include data from the last 30 years. The other 30 files are created by year and are about 1000 records and 10 columns. There are only 2 or 3 people keying in data, never at the same time. We also have a sonic digitizer (measures...

Creating MS Access Database and Tables via SAS
Can someone out there please give me some tips or point me to the best = location for info on how to create a Microsoft Access database and = tables within that database via SAS code? Is this even possible? Once = tables have already been created I know how to access those from SAS, = but it's the creation part that I'm stuck on at the moment. I need that = in order to be able to automate creating new segments for a = distributed/segmented database arrangment. Thanks, s/KAM Hmmm, my prior note probably needs a bit more clarification... What I want to do is completely create and ...

Re: Need for speed: writing to a database with SAS/ACCESS #3
Also try the bulkload option Darryl Putnam IT, Corporate Credit & Risk darryl.putnam@constellation.com (o) 410-468-3637 (c) 301-788-3347 -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Pardee, Roy Sent: Thursday, December 21, 2006 11:53 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: Need for speed: writing to a database with SAS/ACCESS Have you tried playing w/the DBCOMMIT option on the libname statement? Try bumping that up to say, 10k & see if it makes a difference. Other options to play with available here: http://support.sas.com/onlin...

FYI: Avoiding write conflict errors in MS Access clients
This email is semi-off topic; but documents a problem and the server-side solution when using MS Access as a client to many (all?) database servers. I decided to post the problem/solution because the MS Access error message is very misleading. For those of you creating applications using PostgreSQL with MS Access clients: PostgreSQL will allow bit fields to hold null values. I consider this to be valuable because a null boolean field tells me the information is incomplete (a question hasn't been answered, for example); which is, itself, information. MS Access will a...

Re: help SAS/MS ACCESS, general database questions
Without having enough time to analyze excatly, what are your needs: if there are no important reasons why the data must be keept in MS Access: why you don't keep it all in SAS? If you are analyzing in SAS, there is no real reason to have any other DB to hold the data. For users you have many possibilities to give them comfortable possibilities to enter data, in or outside SAS. In SAS, the most easy idea is to use SAS/FSP. But that is not necessary. You can use nearly any tool for data entry and bring the (standard formatted) data into SAS. To get a consultant for realizing a proper solutio...

Re: Mainframe SAS to PC located MS ACCESS database
As is so common in this language, the answer is 'it depends on what you're trying to do'. You can establish an odbc connection from a host job to your SQL Server db (fairly sure it can do Access, too), but 1) your login info is exposed in open code (and while you can do some things to hide it, it's still out there (it's not encrypted)), and 2) if you're transmitting large volumes of data, odbc is not very efficient. If you're only going after a few rows of data, and you don't mind a potential exposure of your credentials, then go the odbc route (this also assu...

Re: Need for speed: writing to a database with SAS/ACCESS #4 680539
Roy, Sigurd, thank you very much! ...

Re: Need for speed: writing to a database with SAS/ACCESS #4 1559623
Dimitri: I don't know whether MySQL supports an ODBC 'bulk load' of data or what you would have to add to the ODBC connection string to make it work, but a bulk load process bypasses rollback enabling and checks of integrity constraints for individual row inserts, deletes, and updates. In many databases systems, bulk loads work many times faster than other database copying methods. As an alternative to ODBC, SAS can write a load file and MySQL can load that (possibly via a SAS pass-thru to MySQL). The MySQL LOAD FILE reportedly works very quickly. Also see http://support.sas.com/...

Re: help SAS/MS ACCESS, general database questions #2
You're describing my project with the Department of Education! We had a dbase database and used a Clipper application to massage it into 'presentable' form. And I thought I was on the bleeding edge (back edge, that is) 8 years ago. As with everything, it depends on what your main purpose is. In my case, it was to disseminate the information to as many people as possible in a format that could be read by/into as many applications as possible. So, we converted our data to flat files and supplied an 'application' that generated sas code that would read the flat files into...

Re: help SAS/MS ACCESS, general database questions #3
Rob: MS Access imports data easily from dBase (*.dbf) files. I see no advantage in moving to SAS for that feature, especially if you would prefer to use the basic DE form controls in MS Access to develop what should be a simple DE capability. Although ODBC transfers of data from MS Access (*.mdb) databases has some pitfalls, as does OLEdb or other middleware (in particular, conversions of integers to floating point numbers, date/time values, and nulls/missings), a bit of Q/C will help you iron out the wrinkles and set up a reliable connection. It often helps as well to separate a data collecti...

ADO Connection Open fails with error -2147467259 when MS Access database under Source Safe
Immediately after generating the Access application from the Source Safe project I get: "-2147467259 Could not use ''; file already in use." If Access database closed and then reopened I get: "-2147467259 The database has been place in a state by user 'Admin' on machine ..... that prevents it from being opened or locked." This error will then persist - even past a machine reboot and compact and repair of both application db (without source safe removal) and 'server' db. If I remove the Access application from Source Safe the ADO connection open...

MATLAB command to access MS Access database
Hi! Is there a MATLAB command which gives access to a .mdb MS Access database or do I need a special driver? Thanks, Andree Hi Andree Ellert, you can use activeXcontrol to interact with MS-Access database. access = actxserver('Access.Application'); returns the handle for Access. set(access, 'Visible', 1); will show the opened Access window. use get(access) and set(access) commands to find the methods available to call. -Vadivelu M =========== http://www.mathworks.com/access/helpdesk/help/toolbox/database/datatool.shtml "Andree Ellert" <ellert@gmx.net////...

request for explanation on access an database in ms access
This is the code that I found on the internet for accessing an ms access database: import java.sql.*; class Test { public static void main(String[] args) { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // set this to a MS Access DB you have on your machine String filename = "d:/java/mdbTEST.mdb"; String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="; database+= filename.trim() + ";DriverID=22;READONLY=true}"; // add on to the end ...

replication problem: target database cannot be accessed during replication
Hi everybody, we are using version 7 of db2 and need to replicate a database. We are using two web-based systems, which access two different databases. One system is used for import data (internal usage), the other system is used to show the imported data via web to the end-user (just reading the database). Both databases are running on different systems (both solaris). Want we want to do is to replicate the import-database (source) to the web-database (target). What we detected is, that during the replication process, the servlet is not able to do any requests to the web-da...

Web resources about - Error Writing to MS Access Database with Replication - comp.soft-sys.sas

Replication (computing) - Wikipedia, the free encyclopedia
... in space , i.e. executed on separate devices, or it could be replicated in time , if it is executed repeatedly on a single device. Replication ...

Facebook Slashes Data Replication With HDFS RAID
Avoiding replication is a key component of efficient data storage , and one method Facebook uses to accomplish this task is HDFS RAID , which ...

PolSci Replication (@PolSciReplicate) on Twitter
Sign in Sign up To bring you Twitter, we and our partners use cookies on our and other websites. Cookies help personalize Twitter content, tailor ...

Encryption in demand for backup and replication: Veeam
When it comes to backup and replication, Veeam has found encryption is one of the most requested feature by businesses.

John Sands Australia cuts data replication time from 14 hours to three
Corporate data replication was taking up to 14 hours to complete at greeting card company John Sands Australia until data acceleration software ...

EMC gives VMware admins the reins to replication and recovery
EMC is putting replication and recovery into the hands of VMware administrators with a software version of its RecoverPoint appliance that's ...

Facebook Slashes Data Replication With HDFS RAID - SocialTimes
Avoiding replication is a key component of efficient data storage , and one method Facebook uses to accomplish this task is HDFS RAID , which ...

rsync.net: ZFS Replication to the cloud is finally here—and it’s fast
As friendly of an online advertisement as you'll find. In mid-August, the first commercially available ZFS cloud replication target became available ...


Violin to bring deduplication, replication, other tools to all-flash storage arrays
Violin Memory plans to add deduplication, thin provisioning, snapshots and other features in a software update for its all-flash storage arrays, ...

Resources last updated: 2/14/2016 8:58:48 PM