Sybase Connection: Data source name not found and no default driver specified

Error Message.jpg

Error message: see the screenshot.

Please Login or Register  to view this content.

I have try to put whole drive path H:\syb-15_0\DataAccess\ODBC\dll\ABCDE.dll inside {}, and it did not work either.

I also found database/srvr information when running Regedit.
http://www.erlandsendata.no/english/...adacconnstring


One more thing: In VBA developer window, there is Reference under the menu Tools, and I am not sure if I need to choose something. I did not find Sybase listed there.

Thanks.

First, you need to change provider from default ADODB to Sybase provider.

Depends on your Sybase... assuming Sybase Adaptive Server
Provider should be... (this replaces Driver parameter)

Please Login or Register  to view this content.

You may also need to set Port (Default I believe is 5000) after srvr.

I'd recommend studying connection string in link below.
https://www.connectionstrings.com/sybase-adaptive/

Also, read up on Sybase specific documentation (Search for terms like "ODBC Connection String" etc).
http://infocenter.sybase.com/help/in...x/CHDEIBCB.htm

I don't have Sybase Server that I can test (haven't dealt with one in at least few years).
But code below is one way to connect and open recordset.

Please Login or Register  to view this content.

Note that as it is indicated in link provided in my previous post.
You may need to specify charset in ".ConnectionString".

Few other things to check/try:
1. Make sure you have correct ODBC driver installed and using correct one (ex. 64Bit installed but trying to use 32Bit..)
http://stackoverflow.com/questions/2...annot-be-found
2. Try replacing "Data Source=ServerName" & "Port=Port#" with "Data Source=\\myserver\myvolume\mypat\mydd.add"
3. Depending on your Sybase driver you'd need different connection string. Hence need to check Sybase documentation.
http://www.erlandsendata.no/english/...adacconnstring

Originally Posted by CK76

I don't have Sybase Server that I can test (haven't dealt with one in at least few years).
But code below is one way to connect and open recordset.

First, I really thank for your time.

What does CursorLocation mean? I have not gone that far yet, at this point, I only want the program to test username/password. All I want to know is if I can access the database, if I only perform one single task, then I can add the task in this portion of code. But, there are multiple tasks, I can choose what to do, no need to do all the tasks.

Code structure: If username/password is correct, then switch to another userform, which has multiple buttons. For example, the other userform has button A (pulling data from table X), button B (pulling data from table Y), button C (loading record to table X), ..... Right now, username/password userform is more like a website login page, if user log in, he/she may do whatever he/she wants to do.

I would follow something like below link. If username/password is correct, then return a message "Username/password is correct"; if it is wrong, return error message "Invalid username/password".

https://www.experts-exchange.com/que...do-in-vba.html

CursorLocation is basically used to set how recordset is accessed/navigated.

3 = adUseClient, recordset is brought to the client at once, and leaves all navigating operation to the client. Adds less overhead to server side. But with large recordsets, this is slower than serverside.
This also means recordset is accessible as read-only (recordset updates to the host are not possible)
2 = adUseServer, navigation is handled by the server. Client communicate to server how to navigate and server does it. On large recordset, this is faster, but adds more overhead to the server.

When using adUseServer, you can also set CursorType... such as:
adOpenStatic - does not allow any updates, inserts or deletions.
adOpenForwardOnly - almost same as above, but you can only scroll forward through records. Improves performance if you need to make only one pass through a recordset.
adOpenDynamic - All type of movement through the recordset is allowed. And additions, changes, deletions by other users are visible.
adOpenKeyset - As adOpenDynamic, but can't see records that other users add. Records deleted by other users are inaccessible from your recordset (data changes are visible).

You'll probably want to read through link if you are not familiar with ADO/ADODB programming.
https://msdn.microsoft.com/en-us/lib...(v=vs.85).aspx

Originally Posted by CK76

I don't have Sybase Server th

It still does not work.

Run-time error 3706 "Provider cannot be found.It may not be installed properly."

Do you know your ODBC provider & driver version for Sybase that you are using?

Without knowing that, can't really help you any further.

Originally Posted by CK76

Do you know your ODBC provider & driver version for Sybase that you are using?

I am not an IT person, where to find such information, here is another screenshot. Earlier screenshot is for "General" tab, and this screenshot is for "About" tab, they are from same window.

Thanks.

Sybase.jpg

Since you are using Sybase ASE 15 instead of the connection string sample I provided. You need something like below.

Please Login or Register  to view this content.

Originally Posted by CK76

Since you are using Sybase ASE 15 instead of the connection string sample I provided. You need something like below.

Please Login or Register  to view this content.

How about cn.open part? The code cannot compile without cn.open

Please Login or Register  to view this content.

Now it goes back to first error: Data source name not found and no default driver specified.

In screenshot, what is the difference between source name and server name?

Thanks.

Server Name/Address will depend on your set up. And also check with whomever that set it up that it's open to external connection.

With DNS set up, server name is often used, but it may require IP address of the server.

I really do recommend that yo scour the Sybase documentation I gave you and verify info with your IT.

You need to set object. As each new connection instance is a separate object. It's done via late binding.
For ADO connection and recordset is separate object and treated as such.
For details, go through the link provided in post #6 of this thread.

If you want to use reference and use early binding method, reference "Microsoft ActiveX Data Objects x.x Library" in your project.

I would not reply on IT department, if they are helpful, I would not have asked questions.

Ok so it may not be IT, but who maintains this Sybase server and DB? Surely someone is administering it. That's the person you should speak to (their title can be IT, DB admin, etc).

This may or may not be helpful, but I've run into similar situations with database drivers, and I've found it easier to just add a linked table in MS Access then ADO to Access.

Please Login or Register  to view this content.

Also, have you added an instance of the database driver to ODBC manager?

C:\Windows\System32\odbcad32.exe

OR

C:\Windows\SysWOW64\odbcad32.exe

duplicate post

Originally Posted by Poizhan

Also, have you added an instance of the database driver to ODBC manager?

C:\Windows\System32\odbcad32.exe

OR

C:\Windows\SysWOW64\odbcad32.exe

Yes............

http://wiki.servicenow.com/index.php....png#gsc.tab=0

Do I need some setting here first? I think this is what VBA code supposed to do. Thanks.

No. ADO is separate from MS Query.
Though both makes use of ODBC driver.