Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Accessing Microsoft Access from Sybase SQL Anywhere 6

8 posts in General Discussion Last posting was on 2002-10-21 04:42:22.0Z
Lorenzo_Louis Posted on 2002-10-09 06:42:13.0Z
From: Lorenzo_Louis
Date: Wed, 9 Oct 2002 02:42:13 -0400
Newsgroups: ianywhere.public.general
Subject: Accessing Microsoft Access from Sybase SQL Anywhere 6
Message-ID: <9E0B1B0B050D524E0024D31285256C4D.0024D32085256C4D@webforums>
Lines: 4
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub ianywhere.public.general:554
Article PK: 2371

does anyone know how to access a microsoft access database from sybase so i
can query the access database and then also query a sybase database.

thanks for any help.


Rick Blythe Posted on 2002-10-09 12:35:37.0Z
From: "Rick Blythe" <rickb@ianywhere.com>
References: <9E0B1B0B050D524E0024D31285256C4D.0024D32085256C4D@webforums>
Subject: Re: Accessing Microsoft Access from Sybase SQL Anywhere 6
Date: Wed, 9 Oct 2002 08:35:37 -0400
Lines: 18
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <VF4lwJ5bCHA.313@forums.sybase.com>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: 172.31.141.16
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub ianywhere.public.general:553
Article PK: 2370

Have a look in the help files. Look for:
ASA User's Guide
PART 4. Database Administration and Advanced Use
CHAPTER 28. Accessing Remote Data
It is explained quite well there.


--
Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer
<Lorenzo_Louis> wrote in message
news:9E0B1B0B050D524E0024D31285256C4D.0024D32085256C4D@webforums...
does anyone know how to access a microsoft access database from sybase so i
can query the access database and then also query a sybase database.

thanks for any help.


Lorenzo_Louis Posted on 2002-10-10 03:10:58.0Z
From: Lorenzo_Louis
Date: Wed, 9 Oct 2002 23:10:58 -0400
Newsgroups: ianywhere.public.general
Subject: Re: Accessing Microsoft Access from Sybase SQL Anywhere 6
Message-ID: <6FE158AACCC759AF00117BF285256C4E.0048FE9785256C4D@webforums>
References: <9E0B1B0B050D524E0024D31285256C4D.0024D32085256C4D@webforums> <VF4lwJ5bCHA.313@forums.sybase.com>
Lines: 11
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub ianywhere.public.general:552
Article PK: 2366

I apologies for my ignorance in advance but I?m a bit new at this.
I have read through that whole section and I?m getting all sorts of errors.
Would you be able to give me an example on how to create a remote server
that connects to an access database?
I have created the server but can?t seem to create the proxy tables. It
always gives me errors when I try to create them saying that it can?t
connect to the specified server but I know its there because I can see it
in Sybase Central.

Can you PLEASE help?

Thanx Lorenzo


Stephen Rice Posted on 2002-10-10 14:05:21.0Z
Subject: Re: Accessing Microsoft Access from Sybase SQL Anywhere 6
From: Stephen Rice <srice_nospam@ianywhere.com>
References: <9E0B1B0B050D524E0024D31285256C4D.0024D32085256C4D@webforums> <VF4lwJ5bCHA.313@forums.sybase.com> <6FE158AACCC759AF00117BF285256C4E.0048FE9785256C4D@webforums>
Organization: iAnywhere Solutions
Message-ID: <Xns92A36422DBFAEsriceianywherecom@199.93.177.77>
User-Agent: Xnews/L5
Newsgroups: ianywhere.public.general
Date: Thu, 10 Oct 2002 10:05:21 -0400
Lines: 36
NNTP-Posting-Host: 172.31.141.72
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub ianywhere.public.general:551
Article PK: 2367

Lorenzo_Louis wrote in
news:6FE158AACCC759AF00117BF285256C4E.0048FE9785256C4D@webforums:

> I apologies for my ignorance in advance but I?m a bit new at this.
> I have read through that whole section and I?m getting all sorts of
> errors. Would you be able to give me an example on how to create a
> remote server that connects to an access database?
> I have created the server but can?t seem to create the proxy tables.
> It always gives me errors when I try to create them saying that it
> can?t connect to the specified server but I know its there because I
> can see it in Sybase Central.
>
> Can you PLEASE help?
>
> Thanx Lorenzo

It is easier if you post your code. We need to see the ODBC DSN for the
access database, the create server statement and the create existing
table statement you are using.

The problem is likely straight forward and probably related to the ODBC
DSN you are using for access since the error is you can't connect to the
remote server. AFAIK creating the remote server does not actually cause
a connection to it so the first time the engine actually tries to use
the DSN is when you go to create the proxy tables

/steve


--
Stephen Rice
Technical Services Manager
iAnywhere Solutions

Whitepapers, TechDocs, bug fixes are all available through the
iAnywhere Developer Community at http://www.ianywhere.com/developer
------ Please Post ------


LorenzoLouis Posted on 2002-10-14 00:30:20.0Z
From: LorenzoLouis
Date: Sun, 13 Oct 2002 20:30:20 -0400
Newsgroups: ianywhere.public.general
Subject: Re: Accessing Microsoft Access from Sybase SQL Anywhere 6
Message-ID: <DDD6FBA83F27B1BD0002C6DE85256C52.005068D585256C4E@webforums>
References: <9E0B1B0B050D524E0024D31285256C4D.0024D32085256C4D@webforums> <VF4lwJ5bCHA.313@forums.sybase.com> <6FE158AACCC759AF00117BF285256C4E.0048FE9785256C4D@webforums> <Xns92A36422DBFAEsriceianywherecom@199.93.177.77>
Lines: 32
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub ianywhere.public.general:548
Article PK: 2369

hi,
my code for creating the server is
CREATE SERVER testasa
CLASS 'asaodbc'
USING 'asa'
i also have an ODBC driver called 'asa' which points to 'c:\lease.mdb', an
access database.

then to create a proxy table
CREATE EXISTING TABLE p_t1
AT 'testasa...table1'
'table1' is in the 'lease.mdb' database but when i run that query i get a
msgbox saying

Error at line 1
Could not access column information for the table 'table1'

that is executed in ISQL 3.2 but then when i go into sybase central and try
to create the proxy table i get the wizard where you choose the remote
server, i choose testasa the i shows me a list o tables that are available
from that server that i can refrence, table1 is now being shown to me in
this list but when i choose it and select next i get a different error then
isql, this error reads

Server testasa:[Microsoft][ODBC Microsoft Access Driver]Syntax error
(missing operator) in query expression 'if
substr(t1.nulls,1,1) = 'Y' then 1 else 0 endif'.

i'm not sure what these two errors mean and how i can fix them.
i tried connecting to another sybase database and that worked fine.

Thankx for any help.


Reg Domaratzki Posted on 2002-10-15 14:54:58.0Z
From: "Reg Domaratzki" <no_rdomarat_spam@sybase.com>
References: <9E0B1B0B050D524E0024D31285256C4D.0024D32085256C4D@webforums> <VF4lwJ5bCHA.313@forums.sybase.com> <6FE158AACCC759AF00117BF285256C4E.0048FE9785256C4D@webforums> <Xns92A36422DBFAEsriceianywherecom@199.93.177.77> <DDD6FBA83F27B1BD0002C6DE85256C52.005068D585256C4E@webforums>
Subject: Re: Accessing Microsoft Access from Sybase SQL Anywhere 6
Date: Tue, 15 Oct 2002 10:54:58 -0400
Lines: 96
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-Mimeole: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <h9uYqzFdCHA.297@forums.sybase.com>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: 172.31.141.144
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub ianywhere.public.general:543
Article PK: 2362

It's the line

CLASS 'asaodbc'

that is causing trouble. This assumes that the "remote" database is an ASA database, which it isn't. Here's the
response I posted in the general newsgroup (sybase.public.sqlanywhere.general) a while back that shows creating a proxy
table definition to the Northwind sample database provided with MS Access.

------

1) I'll assume MS Access has been installed, and the Microsoft Access Driver
(*.mdb) is listed as an available ODBC Driver in the Microsoft ODBC
Administrator when you go under the "Drivers" tab.

2) Created a user ODBC data source called "Northwind" that simply pointed
to the Northwind database. The entries for the data source in the registry
are as follows :
[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\Northwind]
"Driver"="C:\\WINNT\\System32\\odbcjt32.dll"
"DBQ"="C:\\Program Files\\Microsoft Office\\Office\\Samples\\Northwind.mdb"
"Description"="Sample DB"
"DriverId"=dword:00000019
"FIL"="MS Access;"
"SafeTransactions"=dword:00000000
"UID"=""

[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\Northwind\Engines]

[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\Northwind\Engines\Jet]
"ImplicitCommitSync"=""
"MaxBufferSize"=dword:00000800
"PageTimeout"=dword:00000005
"Threads"=dword:00000003
"UserCommitSync"="Yes"

3) Create a new database called msaccess.db with the following command :
dbinit msaccess.db

4) Start the database engine :
dbeng8 msaccess.db
Obviously, your command line will change slightly if you're using ASA6 or ASA7. I think proxy table were first
introduced in ASA v603, so you'll need to be at that version or greater.

5) Connect to the database using DBISQL and execute the following SQL
statements :
CREATE SERVER "ps_Northwind" CLASS 'odbc' USING 'Northwind';
CREATE EXISTING TABLE Shippers AT 'ps_Northwind;C:\Program Files\Microsoft
Office\Office\Samples\Northwind.mdb;;Shippers';



--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

<LorenzoLouis> wrote in message news:DDD6FBA83F27B1BD0002C6DE85256C52.005068D585256C4E@webforums...
> hi,
> my code for creating the server is
> CREATE SERVER testasa
> CLASS 'asaodbc'
> USING 'asa'
> i also have an ODBC driver called 'asa' which points to 'c:\lease.mdb', an
> access database.
>
> then to create a proxy table
> CREATE EXISTING TABLE p_t1
> AT 'testasa...table1'
> 'table1' is in the 'lease.mdb' database but when i run that query i get a
> msgbox saying
>
> Error at line 1
> Could not access column information for the table 'table1'
>
> that is executed in ISQL 3.2 but then when i go into sybase central and try
> to create the proxy table i get the wizard where you choose the remote
> server, i choose testasa the i shows me a list o tables that are available
> from that server that i can refrence, table1 is now being shown to me in
> this list but when i choose it and select next i get a different error then
> isql, this error reads
>
> Server testasa:[Microsoft][ODBC Microsoft Access Driver]Syntax error
> (missing operator) in query expression 'if
> substr(t1.nulls,1,1) = 'Y' then 1 else 0 endif'.
>
> i'm not sure what these two errors mean and how i can fix them.
> i tried connecting to another sybase database and that worked fine.
>
> Thankx for any help.


Lorenzo_Louis Posted on 2002-10-21 04:42:22.0Z
From: Lorenzo_Louis
Date: Mon, 21 Oct 2002 00:42:22 -0400
Newsgroups: ianywhere.public.general
Subject: Re: Accessing Microsoft Access from Sybase SQL Anywhere 6
Message-ID: <E43FEB539638A18F0019D9EC85256C59.005717E085256C53@webforums>
References: <9E0B1B0B050D524E0024D31285256C4D.0024D32085256C4D@webforums> <VF4lwJ5bCHA.313@forums.sybase.com> <6FE158AACCC759AF00117BF285256C4E.0048FE9785256C4D@webforums> <Xns92A36422DBFAEsriceianywherecom@199.93.177.77> <DDD6FBA83F27B1BD0002C6DE85256C52.005068D585256C4E@webforums> <h9uYqzFdCHA.297@forums.sybase.com>
Lines: 2
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub ianywhere.public.general:522
Article PK: 2344

Thankyou vary much for your help that fixed the problem
Thankx heaps, yous have been a great help.


Rick Blythe Posted on 2002-10-14 14:35:52.0Z
From: "Rick Blythe" <rickb@ianywhere.com>
References: <9E0B1B0B050D524E0024D31285256C4D.0024D32085256C4D@webforums> <VF4lwJ5bCHA.313@forums.sybase.com> <6FE158AACCC759AF00117BF285256C4E.0048FE9785256C4D@webforums> <Xns92A36422DBFAEsriceianywherecom@199.93.177.77> <DDD6FBA83F27B1BD0002C6DE85256C52.005068D585256C4E@webforums>
Subject: Re: Accessing Microsoft Access from Sybase SQL Anywhere 6
Date: Mon, 14 Oct 2002 10:35:52 -0400
Lines: 43
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2720.3000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <cNKGWE5cCHA.256@forums.sybase.com>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: 172.31.141.16
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub ianywhere.public.general:547
Article PK: 2365

Can you tell us what version is your Access odbc driver? and what version of
MDAC do you have installed?


--
Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer
<LorenzoLouis> wrote in message
news:DDD6FBA83F27B1BD0002C6DE85256C52.005068D585256C4E@webforums...
hi,
my code for creating the server is
CREATE SERVER testasa
CLASS 'asaodbc'
USING 'asa'
i also have an ODBC driver called 'asa' which points to 'c:\lease.mdb', an
access database.

then to create a proxy table
CREATE EXISTING TABLE p_t1
AT 'testasa...table1'
'table1' is in the 'lease.mdb' database but when i run that query i get a
msgbox saying

Error at line 1
Could not access column information for the table 'table1'

that is executed in ISQL 3.2 but then when i go into sybase central and try
to create the proxy table i get the wizard where you choose the remote
server, i choose testasa the i shows me a list o tables that are available
from that server that i can refrence, table1 is now being shown to me in
this list but when i choose it and select next i get a different error then
isql, this error reads

Server testasa:[Microsoft][ODBC Microsoft Access Driver]Syntax error
(missing operator) in query expression 'if
substr(t1.nulls,1,1) = 'Y' then 1 else 0 endif'.

i'm not sure what these two errors mean and how i can fix them.
i tried connecting to another sybase database and that worked fine.

Thankx for any help.