Connecting to Sybase database

sdk development
1

Does anyone out there have a little sample code :slight_smile:

I want to upload/use the data fetched from VBA & connect to my sybase database and load the data into a table…

Meanwhile, I will work on the following
link… to see whether I can change this code from Oracle to sybase…

Ravi Amara :us: (BOB member since 2002-10-02)

2 
Option Explicit

Dim Cnn_AP_RPT As ADODB.Connection
Dim sql As String

Sub Create_table_of_2_weeks_logged_Time()
Open_AP_RPT
sql = "drop table logged_time_table"
Cnn_AP_RPT.Execute sql
sql = "select * into logged_time_table from another_logged_time_table "
sql = sql + "where log_entry_dt between '" + Format(DateAdd("ww", -2, Now), "dd mmm yyyy") + "' and '" + Format(Now, "dd mmm yyyy") + "'"
Cnn_AP_RPT.Execute sql
Close_AP_RPT
End Sub

Sub Open_AP_RPT()
Set Cnn_AP_RPT = New ADODB.Connection
Cnn_AP_RPT.Open "dsn=prod ap_rpt;UID=myUserid;PWD=myPassword"
End Sub

Sub Close_AP_RPT()
Cnn_AP_RPT.Close
End Sub

The above example uses reference: Microsoft ActiveX data objects 2.1 Library. It opens the database, executes the sql and closes the db. You can use later versions of the reference.

Pauline :australia: (BOB member since 2002-08-20)

3

I see what you’re doing, what do I enter for dsn, It is it the server name?
:confused:

qwerty (BOB member since 2003-01-30)

4

I did get this to work, thanks :slight_smile:

Is it possible though, to use an existing connection that the report already uses, to insert into sybase?

I have extracted the existing document connection with the following code,

Dim connection_formula As String
Dim s_connection As String
connection_formula = “=Connection (” + Chr(34) + “DataproviderName” + Chr(34) + “)”
Debug.Print ThisDocument.Evaluate(connection_formula)
s_connection = ThisDocument.Evaluate(connection_formula)

Thanks

qwerty (BOB member since 2003-01-30)

5

ADO expects a connection to either an ODBC data store or an OLE DB provider. The default provider is ODBC. If you don’t want to set up the odbc SYSTEM DSN, you can refer to the ODBC driver in your connection string.

My universe connections use OpenClient which I never use outside freehand sql or standard data providers. I don’t even know if I could.

If you read about ADO in the MS knowledgebase, it might clarify things…

Pauline :australia: (BOB member since 2002-08-20)

6

If I don’t want to specify a DSN, how to I get the information from the connection string, do you have any sample code that I can take a look at?

Thanks

qwerty (BOB member since 2003-01-30)

7

I might have mislead you, what I meant is that you can set the driver in the connection string like in this example:

Naturally, your driver would be your sybase one. In this case, you don’t have to have the system DSN set up.

Pauline :australia: (BOB member since 2002-08-20)