Question Re: VBA Access Sybase SQL Anywhere 17 in Technology Q&A

VBA Access Sybase SQL Anywhere 17

former_SQLA_member1694943 — Mon, 05 Oct 2020 04:53:54 GMT

Hello, I tried to access sybase via MSADO 2.8 and SQL Anywhere17 Driver, but was not successful. Obviously it sets up a connection (there is no error message) but it does not except the SELECT etc. It says: the syntax is wrong. Even if the syntax is the same as in MS Query / Excel. There it works via SQL Anywhere 17 ODBC and I can edit the values in the fields of Sybase DB. Does anybody have an advise? I would be very thankful. Johannes

Re: VBA Access Sybase SQL Anywhere 17

Vlad — Mon, 05 Oct 2020 07:55:22 GMT

Can you start the DB server using the key "-zr" and check what SQL query comes from MSADO? Maybe it comes with "square brackets":

dbsrv17 -zr sql test.db

Re: VBA Access Sybase SQL Anywhere 17

former_SQLA_member1694943 — Mon, 05 Oct 2020 09:46:08 GMT

Hello Vlad, I was able to start test.db from command line (cmd). Now it works. But how do I check SQL Query which comes from MS ADO? Johannes

Re: VBA Access Sybase SQL Anywhere 17

Breck_Carter — Mon, 05 Oct 2020 10:05:13 GMT

In your command line, start dbsrv17 with these extra options

-zr sql -zo "c:\\temp\\logsql.txt"

Re: VBA Access Sybase SQL Anywhere 17

Breck_Carter — Mon, 05 Oct 2020 10:09:55 GMT

You can also turn -zr on and off dynamically, after the server has started; see the Help Topic Request Logging to learn about CALL sa_server_option().

. . . request logging is a wonderful tool for desperate times 🙂

Re: VBA Access Sybase SQL Anywhere 17

former_SQLA_member1694943 — Mon, 05 Oct 2020 10:36:40 GMT

after cmd command: >dbsrv17 -zr sql -zo "c:\\temp\\logsql.txt" test1.db - the logsql.txt is empty. Any suggestions? Thank you

Re: VBA Access Sybase SQL Anywhere 17

VolkerBarth — Mon, 05 Oct 2020 11:24:31 GMT

Is it still empty when you try to access the database via MS Query and/or your application? Because the log will only list database requests so just starting the database via dbsrv17 does not lead to any database request.

Re: VBA Access Sybase SQL Anywhere 17

Breck_Carter — Mon, 05 Oct 2020 13:48:12 GMT

In addition to Volker's question, you can log everything (not just sql) this way:

dbsrv17 -zr all -zo "c:\\temp\\logsql.txt" test1.db

Re: VBA Access Sybase SQL Anywhere 17

former_SQLA_member1694943 — Tue, 06 Oct 2020 04:35:28 GMT

Thank you, Volker, so far, when I start the database via dbsrv17... I am not anymore able to use an DataSource connector in order to connect Excel/MSQuery. But if I try to connect via VBA connection string (sConnStr = "DRIVER=SQL Anywhere 17;Database=C:\\Users......\\test1.db;UID=test1User;PWD=testpwd" ) - the log file contains entries. It says, that the correlation name (of the target table) is missing. I do not have extended knowledge in this field, this is the point, I think. Generally I am looking for a proper way to communicate with Sybase via VBA, as it works (very god!) with MSSQL and SQLite. Thanks very much to everybody for your precious help so far. Now I have to prepare myself for a business trip. I will try to continue afterwards. Johannes

Re: VBA Access Sybase SQL Anywhere 17

former_SQLA_member1694943 — Tue, 06 Oct 2020 04:36:38 GMT

thank you, Breck, very much

Re: VBA Access Sybase SQL Anywhere 17

former_SQLA_member1694943 — Tue, 06 Oct 2020 04:40:15 GMT

thank you, Vlad, very much so far

Re: VBA Access Sybase SQL Anywhere 17

Breck_Carter — Wed, 07 Oct 2020 11:21:36 GMT

Please start a new question. Include ALL the copy/pasted text of your code for connecting, AND the log file.

You can format the code with explicit PRE tags...

<PRE>

code
more code
and so on

</PRE>

Re: VBA Access Sybase SQL Anywhere 17

Breck_Carter — Wed, 07 Oct 2020 11:27:39 GMT

When you get back, if you just want to send me all your data via email, I can either (a) post an answer here, or more likely (b) post a formatted question for you... breck dot carter at gmail.

Re: VBA Access Sybase SQL Anywhere 17

former_SQLA_member1694943 — Mon, 02 Nov 2020 06:22:02 GMT

'starts the server etc.

sConnStr = "DRIVER=SQL Anywhere 17;Database=C:\\ProgramData...\\xxx.db;ENG=..DBSRV;DBN=Da;UID=yyy;PWD=xxx" 'starts the server etc. it works 20201023

'connects to a running server

sConnStr = "DRIVER=SQL Anywhere 17;ENG=..DBSRV;DBN=..;UID=yyy;PWD=xxx" 'connects to a running server, it works 20201023

Thank you everybody for your precious help!

Re: VBA Access Sybase SQL Anywhere 17

VolkerBarth — Mon, 02 Nov 2020 07:51:38 GMT

Note, this will only work when the database is expected to run on the local machine, i.e. not on a network server because a SQL Anywhere database client cannot itself start a database server on a remote machine.

Note, if the database is expected to run only locally, you can combine that into one connection string via using the START connection parameter, such as...

"START=dbeng17 -xd -c 16M;Server=DBSRV;DBF=C:\\ProgramData...\\xxx.db;DBN=Da;UID=yyy;PWD=xxx"

Re: VBA Access Sybase SQL Anywhere 17

former_SQLA_member1694943 — Mon, 02 Nov 2020 09:35:14 GMT

thank you Volker. May be I will have same more questions in the future. Johannes