VBA Access Sybase SQL Anywhere 17

[former_SQLA_member1694943]

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

[Vlad]

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

[former_SQLA_member1694943]

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

[Breck_Carter]

In your command line, start dbsrv17 with these extra options

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

[Breck_Carter]

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 🙂

[former_SQLA_member1694943]

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

[VolkerBarth]

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.

[Breck_Carter]

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

[former_SQLA_member1694943]

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

[former_SQLA_member1694943]

thank you, Breck, very much

[former_SQLA_member1694943]

thank you, Vlad, very much so far

[Breck_Carter]

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>

[Breck_Carter]

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.

[former_SQLA_member1694943]

'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!

[VolkerBarth]

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"

[former_SQLA_member1694943]

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