Running a query from VBA to hit a Sybase DB 1

[Orion45]

I am making a small Access application for a project. Due to the security level involved I cannot use linked tables so I need to make the application completely transactional. However, the Database I'm trying to query is on a Sybase server. I have tried to write query statements but have not been able to get them to work.

strSQL = "SELECT sl.status FROM master..syslogins sl WHERE sl.name = '" & Login & "';"

I think I'm on the right track but I'm not sure. Has anyone done this before? Any help is appreciated.

 

[cmmrfrds]

If you are not linking the Sybase tables in Access, how to you plan on connecting to Sybase?

 

[Orion45]

The Sybase DB is connected to my ODBC settings. There should be a way to connect to that particular DB through the ODBC connection instead of using linked tables. My thought was that by giving a relative location in a SQL statement it should be able to find my DB and table. I am currently running stored procs this way but would also like the flexibility of running SQL statements from VBA. What do you think?

 

[cmmrfrds]

First you need to establish a connection to Sybase. In the case, of linked tables ODBC is taking care of that for you. One way is to include a reference to the ODBC DSN or however the connection is built.

My preference is to use OLE-DB which is front ended by ADO. An ADO connection uses the OLE-DB Provider to connect with the database. If you have the Sybase Client installed then there should be a Sybase OLE-DB provider on your PC. The only thing that ADO in Access needs is the connection string set up for Sybase.

Use the UDL Wizard to set up the connection string.

Here is how you invoke the udl wizard. Do this on your desktop.

1. create a blank notepad file.
2. save the file.
3. rename the file by adding a new extention of .udl
make sure you save as all files not txt or some other
file type.
4. the icon for the file should change from notepad to a
little computer - it has now become the wizard.
5. double click on the file and it will bring up a dialog
box with multipule tabs.
6. use the Sybase provider - hopefully version 12.5 or >.
7. look under the ALL tab and there will be parameter settings which you can change if necessary. There is a test
button, press to test the connection.
8. close the file.
9. open the file from notepad instead of double clicking the icon. You will see the connection string which you can copy and paste into your program.

 

[Orion45]

Perhaps I phrased my question incorrectly. I already have a working ADODB connection string which I call to run Sybase stored proceedures. My question is how to use this connection to run queries? I understand the logic but am not sure of the syntax. Thanks again for all your help.

 

[cmmrfrds]

If you mean how do you build a query and run it on the ado connection, here is an example. This is using sql server.

Dim cn As New ADODB.Connection, sql1 As String
Dim rs As New ADODB.Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

sql1 = "select * from firsttab"

rs.Open sql1, cn, adOpenStatic, adLockOptimistic
'- OR
cn.Execute sql1

If you mean an existing Access Query, Then, you can pick it out of the Views collection in the ADOX catalog.

Dim cn As New ADODB.Connection, sql1 As String
Dim rs As ADODB.Recordset, connString As String
Set rs = New ADODB.Recordset

''- Microsoft ADO Ext. 2.6 for DDL and Security
Dim cg As New ADOX.Catalog
Set cg.ActiveConnection = CurrentProject.Connection

Dim v As View
Dim vn As View
For Each v In cg.Views
''Debug.Print "views = "; v.Name
If v.Name = "query1" Then
Set vn = v
End If
Next

rs.Open vn.Name, connString, adOpenForwardOnly, adLockReadOnly