f



MS Access: ODBC or NO ODBC

Hi,

I have two MS Access 2000 databases on my desktop.  I need the data from
both.  I use the following code to read a table from the first db:

PROC IMPORT OUT=table1
       DATATABLE ="table1"
    DBMS = ACCESS2000 REPLACE;
       DATABASE="C:\Documents and Settings\Desktop\db1.mdb";
RUN;

This works fine.

For my second database, using the same code but changing the file/table
names, I get the following error:

ERROR: Open cursor: ODBC--connection to 'DB2' failed.
ERROR: Import unsuccessful.  See SAS Log for details.

When do I need to use ODBC and when do I not need it for reading Access
databases?  Is there some attribute of the 2nd database that I can change
so that I don't require ODBC?

Thanks,
EP
0
5/5/2009 3:58:12 PM
comp.soft-sys.sas 142828 articles. 3 followers. Post Follow

0 Replies
666 Views

Similar Articles

[PageSpeed] 38

Reply:

Similar Artilces:

Re: MS Access: ODBC or NO ODBC
Hi Ernie, You probably have checked the easy stuff already. But just setting aside major connectivity issues for the moment, have you checked things like the table name? i.e. MSAccess can have spaces in the table names, SAS can't, but SAS has the 'table name'n notation for just such occurances. The failure could be connection or just a no match which will also fail the connection. Hope this is helpful. Mark Terjeson Investment Business Intelligence Investment Management & Research Russell Investments 253-439-2367 Russell Global Leaders in Multi-Manager Investing -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Ernie P. Sent: Tuesday, May 05, 2009 8:58 AM To: SAS-L@LISTSERV.UGA.EDU Subject: MS Access: ODBC or NO ODBC Hi, I have two MS Access 2000 databases on my desktop. I need the data from both. I use the following code to read a table from the first db: PROC IMPORT OUT=table1 DATATABLE ="table1" DBMS = ACCESS2000 REPLACE; DATABASE="C:\Documents and Settings\Desktop\db1.mdb"; RUN; This works fine. For my second database, using the same code but changing the file/table names, I get the following error: ERROR: Open cursor: ODBC--connection to 'DB2' failed. ERROR: Import unsuccessful. See SAS Log for details. When do I need to use ODBC and when do I not need it for reading Access databases? Is there some attribute of the 2nd database that I can ...

Re: MS Access: ODBC or NO ODBC #6
Hi, I called the databases DB1 and DB2 just for illustrative purposes. I should have known better than to use DB2. The actual names are something different. They are definitely entirely different databases and tables. I was able to rename the problem database without any issues. Thanks, EP ...

Re: MS Access: ODBC or NO ODBC #8
Your original message has a log snip that says look to the sas log for details. What does it say? You should be able to have multiple odbc connections one right after the other--they execute one at a time. Or they should, unless MS Access is doing something weird behind the scenes--and the log may be able to tell us that (like, connection refused). CH On Tue, 5 May 2009 11:58:12 -0400, Ernie P. <aaronevans@HOTMAIL.COM> wrote: >Hi, > >I have two MS Access 2000 databases on my desktop. I need the data from >both. I use the following code to read a table from the first db: > >PROC IMPORT OUT=table1 > DATATABLE ="table1" > DBMS = ACCESS2000 REPLACE; > DATABASE="C:\Documents and Settings\Desktop\db1.mdb"; >RUN; > >This works fine. > >For my second database, using the same code but changing the file/table >names, I get the following error: > >ERROR: Open cursor: ODBC--connection to 'DB2' failed. >ERROR: Import unsuccessful. See SAS Log for details. > >When do I need to use ODBC and when do I not need it for reading Access >databases? Is there some attribute of the 2nd database that I can change >so that I don't require ODBC? > >Thanks, >EP ...

Re: MS Access: ODBC or NO ODBC #9
Hi, I set MSGLEVEL to 'I' and also specified the PRINTMSGLIST option. I'm unaware of how to get SAS to print more detailed msgs to the log. This has bothered me for a long time since I'm already looking at the SAS LOG when I read "See SAS LOG for details" Thanks, EP ...

Re: MS Access: ODBC or NO ODBC #3
Hi Mark, All of my tables have underscores in place of spaces. Thanks, EP ...

Re: MS Access: ODBC or NO ODBC #5
could it be a locking problem? What does the renaming of the .mdb file bring, any changes? Gerhard On Tue, 5 May 2009 09:28:38 -0700, Terjeson, Mark <Mterjeson@RUSSELL.COM> wrote: >I figured you already checked the easy stuff >but just makin' sure. > >Mark :o) > > >-----Original Message----- >From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of >Ernie P. >Sent: Tuesday, May 05, 2009 9:25 AM >To: SAS-L@LISTSERV.UGA.EDU >Subject: Re: MS Access: ODBC or NO ODBC > >Hi Mark, > >All of my tables have underscores in place of spaces. > >Thanks, >EP ...

Re: MS Access: ODBC or NO ODBC #7
strange thing... If you start a new SAS session and switch the 2 DBs, is it possible to read DB2 in that case? And do you get the error afterwords in DB1? Maybe that "cursor" is an open (not properly closed) cursor from SAS? Normally a cursor is a buffer which is defined and used in programs to access a DB-table. I used that for DB2 from PL1 and other languages on mainframes. Maybe SAS and / or the ODBC-connection can only handle one cursor at a time? Gerhard On Tue, 5 May 2009 12:29:30 -0400, Aaron Evans <aaronevans@HOTMAIL.COM> wrote: >Hi, > >I called the databases DB1 and DB2 just for illustrative purposes. I >should have known better than to use DB2. The actual names are something >different. > >They are definitely entirely different databases and tables. I was able to >rename the problem database without any issues. > >Thanks, >EP ...

Re: MS Access: ODBC or NO ODBC #2
I'm not sure about that, but I think the SAS-Access-to-MS-ACCESS IS a ODBC connection! You don't need to do anything. The error message is a bit strange: "...ODBC--connection to 'DB2' failed...". That has nothing to do with DB2 (the IBM database), but maybe the name of the second DB is DB2.mdb (?). Can't imagine that SAS gets in trouble with that. But it seems to be a synchronization problem. The "open cursor" usually means, that a task has locked the DB or at least a part of it. Don't think that MS-Access has sophisticated tools for locking parts of a DB, so I think that any other program has locked the complete DB. Maybe it is possible to lock at table-level? One question: is that really a second DB? Or is it another table in the same DB? What do you change, the DATABASE and DATATABLE? Or only one of them? Try to rename the whole DB (only a idea). Then any locking should be removed or the rename is impossible... Gerhard On Tue, 5 May 2009 11:58:12 -0400, Ernie P. <aaronevans@HOTMAIL.COM> wrote: >Hi, > >I have two MS Access 2000 databases on my desktop. I need the data from >both. I use the following code to read a table from the first db: > >PROC IMPORT OUT=table1 > DATATABLE ="table1" > DBMS = ACCESS2000 REPLACE; > DATABASE="C:\Documents and Settings\Desktop\db1.mdb"; >RUN; > >This works fine. > >For my second database, using the same co...

Re: MS Access: ODBC or NO ODBC #10
Hi, I noticed today that for the database I can successfully read in SAS, under table type it says "Table: Linked Access" For the database that causes the error, it says "Table: Linked ODBC" Thought this information might help generate ideas. Thanks EP ...

Re: MS Access: ODBC or NO ODBC #4
I figured you already checked the easy stuff but just makin' sure. Mark :o) -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Ernie P. Sent: Tuesday, May 05, 2009 9:25 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: MS Access: ODBC or NO ODBC Hi Mark, All of my tables have underscores in place of spaces. Thanks, EP ...

ODBC or not ODBC
Do you know, other methods to connect Mysql DB without driver ODBC? I use Visual Basic 6.0? Thanks. Andrea > Do you know, other methods to connect Mysql DB without driver ODBC? > I use Visual Basic 6.0? What is your problem exactly? I have worked on quite a few VB6 application that used ODBC. ODBC works well enough. Best regards. There are other ways to connect to database (read and write date)? Thanks Andrea "Dikkie Dik" <dikkie@nospam.org> ha scritto nel messaggio news:49cb82bf$0$20534$bf4948fe@news.tele2.nl... >> Do you know, other methods to conne...

Which ODBC Drivers? (Unix SAS/Access to ODBC for Oracle & Informix)
Having read various SAS documentation and called SAS TS I am still less than crystal clear on what I consider to be a straightforward question: We are running SAS 9.1.3 on a Unix Solaris 64bit box. We wish to access Oracle data and Informix data from SAS using SAS/Access to ODBC, thereby eliminating the need to licence two SAS/Access products. The solution does not need to have particularly great performance as we will be using it overnight on small data volumes on an occasional basis. We have a trial SAS/Access to ODBC licence and the module will soon be installed. My questions involve the...

accessing ms access via ODBC without Microsoft Access
Hi, If I have a computer without Microsoft Access installed but with an ODBC connection to a .mdb file - can I still access the 'database' via ODBC? thanks Tim <timasmith@hotmail.com> wrote in message news:1145206985.433826.106120@i40g2000cwc.googlegroups.com... > Hi, > > If I have a computer without Microsoft Access installed but with an > ODBC connection to a .mdb file - can I still access the 'database' via > ODBC? Yes, provided you are running a program prepared in a language that supports ODBC and have installed an ODBC driver on that user'...

Read-Only ODBC Access to MS Access 2000
I have created an ODBC File DSN data source in Windows XP. I have successfully used this DSN to link tables from a Sybase SQL Anywhere database to Access 2000. How can you make this ODBC connection read-only? P.S. I would like to make the ODBC connection itself read-only (if possible) rather than adding security to the MS Access DB. ...

SAS/ACCESS ODBC
I am uncertain about setting up a DSN for use in a LIBNAME statement. One might use: LibName Ora Oracle User = me PW = none readbuff = 1000 ; or Libname Excel ODBC Access = ReadOnly NoPrompt = 'DSN=Excel Files; DBQ=C:\Documents and Settings\Test.xls' ; In the first example, the statement is not "pointing" to the actual file (database) in contrast to the use of DBQ in the second. It may be the difference in the SAS products. However, for a Cache database employed by a program (Intersystems and Axis Clinical, respectively), I am having difficulties finding the file to wh...

Using Iseries Access ODBC Driver with MS ACCESS 2003
When Trying attaching databases into MsAccess2003 using Iseries Access ODBC Driver (I'm in OS400 v5r2) I get an error message according to which the duration of connection is too long and so I can't attach the database although I got the list of the tables in the library I specified. Can anyone help me ? Thanks in advance. The timeout to dispay de data is small. You need to increase this timeout. The problem can occur when the tables are very very very (but very) big. "Bertrand Bard" <bbard@dehon.com> escribi� en el mensaje news:d01a5cc.0404280030.58f11d3e@posting.go...

accessing MS Access database fila via ODBC problem
Hi! I'm trying to connect to a MDB file via ODBC like this: char *DSN = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\\dev-cpp\\test\\1.mdb"; SQLAllocEnv(&env); SQLAllocConnect(env, &hdbc); SQLBrowseConnect(hdbc, (UCHAR*)DSN, strlen(DSN), (UCHAR*)buf, BUF_SIZE, (SQLSMALLINT*)&rcvlen); The function SQLBrowseConnect fails and SQLGetDiagRec returns "IM001 Driver does not support this function" Can anyone help me? Thanks! David On S...

MS-Access, ODBC access with Visual C++ 6.0, and external actions on database
[was redirected here] I have a problem that I can distill down to the following. 1/ A Visual C++ 6.0 program connects to a MS Access database using an ODBC connection and sleeps for a long time. The connection takes all defaults and uses CDatabase::useCursorLib in the OpenEx command. 2/ The database is well over 400MB large with over 200 tables and is compacted. 3/ Another process starts a winzip on that database that takes about 45 to 60 seconds to complete (wzzip -a -ex aaa.zip aaa.mdb). 4/ The Windows Task Manager shows 100s of MBs of available memory being consumed....

MS Access odbc issues
I am having trouble with MS Access 2000 on Windows 2000 Pro. The two problems are that it is taking very long to link to Informix and Oracle through ODBC and queries from Informix and Oracle never finish - the meter shows 99% complete - for hours and hours. I have the latest ODBC drivers; defragged and chkdsk'ed the hard drive; reinstalled Office. Any other suggestions, hints, tips? TIA Pete On 28 Jan 2004 22:42:25 -0800, peteg@garlic.com (hedrew3) wrote: >I am having trouble with MS Access 2000 on Windows 2000 Pro. > >The two problems are that it is taking very long to l...

MS Access ODBC problem
I need to link some tables from a remote MySql server (ver. 4.1.12) to a MS Access 2003 db. I can succesfully connect using both MySql Administrator and MySql Query Browser. Even the ODBC Admin test button tells "Success; connection was made". But when I try to link the tables from within MS Access using the ODBC DSN I get: [MySQL][ODBC 3.51 Driver]Access denied for user 'xxx'@'%' to database 'yyy' (#1044) I searched the Net without results so far. Any help will be really appreciated. Thanks, Bruno On Wed, 23 Nov 2005 17:43:05 +0100, Bruno wrot...

SAS/Access-ODBC Options?
I get the following when I use a libname with the odbc engine and access an oracle table: =20 ERROR: This DBMS table or view cannot be accessed by the SAS System because it contains column names that are not unique when a SAS normalized (uppercased) compare is performed. See "Naming Conventions" in the SAS/ACCESS documentation. =20 Looking in SQL Plus, the closest thing to duplication I have is something like the columns PARTY_NAME and PARTY_TIME. I've tried various combinations of the validvarname system option and the preserve_names/preserve_col_names libn...

MS Access ODBC Driver
I need to export MySQL data to Access. As always, there seems to be more than one way to do this. I notice that PHP has: DB.php If I was doing this in perl, there would be an ODBC driver (DBD::ODBC). Is there such a thing in PHP? Can this play well with DBO? I see some commercial driver products, but my needs are modest as I'm not using any of Accesses special features, whatever those might be! Jeff In article <8cWdnZ0uWcbRty3VnZ2dnUVZ_uKdnZ2d@earthlink.com>, Jeff <jeff@spam_me_not.com> wrote: > I need to export MySQL data to Access. > > ...

MS OLE DB for ODBC with Oracle ODBC source headaches
Hi all, I am having trouble getting linked Oracle 9 server in MS SQL Server 2005 Express to work properly. My machine is running Windows XP. The Microsoft and Oracle OLE DB Providers have problems dealing with Oracle's Numeric Data Type, so I decided to use Microsoft's OLE DB for ODBC Provider and an Oracle ODBC source. When using the Microsoft ODBC for Oracle Driver in my ODBC source I have inconsistent behavior. Sometimes my queries are processed properly, then other times I get the following error OLE DB provider "MSDASQL" for linked server "ODBCBEAST" returne...

RE: Ingres and ODBC Date problems from W2K ODBC access.
Mark. Have you tried on the ASP Server.LCID = 2057? It may be something in the ASP, where ASP assumes all DATEs (by default) are MM/DD/YYYY hh:mm:ss. Server.LCID makes ASP treat DATE types (CDATE) to use DD/MM/CCYY hh:mm:ss. Have a go and let me know. Regards, Jason -----Original Message----- From: mark [mailto:fugacious@yahoo.com] Sent: Tuesday, 14 October 2003 9:03 AM To: info-ingres@ams.org Subject: Re: Ingres and ODBC Date problems from W2K ODBC access. Thank you to all the people who have responded and also e-mailed me so far. I have checked II_DATE_FORMAT and this is currently set to MULTINATIONAL and is working well on another server with (apparently) identical software configuration, (W2K server with SP4) the hardware differs in that the newer server has dual P2.6 CPU's and is superior in other hardware aspects namely memory and disk. The date format I would like to extract from an Ingres query is DD/MM/YYYY hh:mm:ss What I have now is the following statement within our in house developed dll and asp pages SELECT date('now') As mentioned previously the above statement does return the correct date in both the dll and asp page however for some unknown reason there is a different format between the two. I would like the format to be consistient between asp pages and compiled dll's.(NOTE: On both machines if I start the VDBA and enter the above statement in the SQL window they both return the date from i...

Web resources about - MS Access: ODBC or NO ODBC - comp.soft-sys.sas

Resources last updated: 3/16/2016 6:56:13 AM