vb6 and sybase ?? - basic.visual

"Uwe Gutsche" <uwe.gutsche@mailbox.tu-dresden.de> wrote in message
news:5ab10eF2nvhsfU1@mid.dfncis.de...
> Hallo,
>
> I have a nice application (in VB6) that uses an ACCESS-Database.
> Now there must other (always ready) existing applications use this
> database and thatswhy it will converting into a SYBASE-Database.
> My Problem is: how to use the SYBASE-Database in my program ? (I've
> never done bevore)
> Has anybody a "cooking-instruction" or an exempel? (it is important for
me)
>
> Thanks,
> Uwe Gutsche
>

Depends on the driver/providers you have available.
http://www.connectionstrings.com/?carrier=sybase

-ralph

"Uwe Gutsche" <uwe.gutsche@mailbox.tu-dresden.de> wrote in message
news:5ab10eF2nvhsfU1@mid.dfncis.de...
> Hallo,
>
> I have a nice application (in VB6) that uses an ACCESS-Database.
> Now there must other (always ready) existing applications use this
> database and thatswhy it will converting into a SYBASE-Database.
> My Problem is: how to use the SYBASE-Database in my program ? (I've
> never done bevore)
> Has anybody a "cooking-instruction" or an exempel? (it is important for
me)
>
> Thanks,
> Uwe Gutsche
>

Depends on the driver/providers you have available.
http://www.connectionstrings.com/?carrier=sybase

-ralph

Uwe Gutsche wrote:
> Hallo,
>
> I have a nice application (in VB6) that uses an ACCESS-Database.
> Now there must other (always ready) existing applications use this
> database and thatswhy it will converting into a SYBASE-Database.
> My Problem is: how to use the SYBASE-Database in my program ? (I've
> never done bevore)
> Has anybody a "cooking-instruction" or an exempel? (it is important
> for me)
> Thanks,
> Uwe Gutsche

First, you need to determine what version of Sybase...
Sybase have started developing their own ODBC driver
for the later versions (12.x ++)...

I will assume that you already have the Sybase ODBC drivers installed
On your VB machine.

I will also assume that you have not made the mistake of using VB's
Bound data-controls, but use ADO directly. (NOT DAO).

Here's how to make a connection to the db:

Dim sConn As String
sConn = "Provider=Sybase.ASEOLEDBProvider.2;"
sConn = sConn & "Server Name192.168.1.50;" 'Machine or IP
sConn = sConn & "Server Port Address=5000;"
sConn = sConn & "Initial Catalog=DataBaseName;"
sConn = sConn & "User ID=username;"
sConn = sConn & "Password=myPassword;"
sConn = sConn & "Persist Security Info=True;"

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open sConn

From here on, the rest is excactly like before:

Dim rs as ADODB.RecordSet
Dim sql as string

sql = "SELECT Name, Phone FROM Customers"
Set rs = cn.Execute(sql)
While Not rs.EOF
Debug.Print rs("Name") & vbTab & rs("Phone")
rs.MoveNext
Wend
rs.Close
...
cn.Close
Set rs = Nothing
Set cn = Nothing

Be aware of:
Sybase TransactSQL is Case sensitive on Table/Procedure/Column names
Sybase TSQL differs quite radically from Access SQL in some areas.

(So check all your queries).

HTH...

--
Dag.

Uwe Gutsche wrote:
> Hallo,
>
> I have a nice application (in VB6) that uses an ACCESS-Database.
> Now there must other (always ready) existing applications use this
> database and thatswhy it will converting into a SYBASE-Database.
> My Problem is: how to use the SYBASE-Database in my program ? (I've
> never done bevore)
> Has anybody a "cooking-instruction" or an exempel? (it is important
> for me)
> Thanks,
> Uwe Gutsche

First, you need to determine what version of Sybase...
Sybase have started developing their own ODBC driver
for the later versions (12.x ++)...

I will assume that you already have the Sybase ODBC drivers installed
On your VB machine.

I will also assume that you have not made the mistake of using VB's
Bound data-controls, but use ADO directly. (NOT DAO).

Here's how to make a connection to the db:

Dim sConn As String
sConn = "Provider=Sybase.ASEOLEDBProvider.2;"
sConn = sConn & "Server Name192.168.1.50;" 'Machine or IP
sConn = sConn & "Server Port Address=5000;"
sConn = sConn & "Initial Catalog=DataBaseName;"
sConn = sConn & "User ID=username;"
sConn = sConn & "Password=myPassword;"
sConn = sConn & "Persist Security Info=True;"

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open sConn

From here on, the rest is excactly like before:

Dim rs as ADODB.RecordSet
Dim sql as string

sql = "SELECT Name, Phone FROM Customers"
Set rs = cn.Execute(sql)
While Not rs.EOF
Debug.Print rs("Name") & vbTab & rs("Phone")
rs.MoveNext
Wend
rs.Close
...
cn.Close
Set rs = Nothing
Set cn = Nothing

Be aware of:
Sybase TransactSQL is Case sensitive on Table/Procedure/Column names
Sybase TSQL differs quite radically from Access SQL in some areas.

(So check all your queries).

HTH...

--
Dag.

If your tables are going to be identical, it's possible the simplest
approach would be to take a copy of the Access database you are using and
link the tables to the Sybase tables (using ODBC drivers for the Sybase DB)
and continue to use your application, with the Access database. That should
work just fine for either DAO or ADO, with minimal change required to your
application.

A well-designed, well-implemented single-user or multi-user application just
converted to client-server (as you'll be doing) with no tweaks is likely not
to be a well-designed, well-implemented client-server because of the
difference in the way file-server databases (like Jet, aka Access) operate
and the way server DBs (like all flavors of Sybase) operate. But, often,
the changes required are not a lot of work to implement, but do yield much
better response.

You can review presentations I did for my user group regarding Access in a
Multiuser Environment and Access as a Client, as well as one on Access or VB
as a front-end/client at http://appdevissues.tripod.com in the Downloads
section. The first two deal with Access, but many/most of the "issues" are
the same because it is a matter of using the DB, not so much the "front-end"
application generator.

Larry Linson
Microsoft Access MVP


"Ralph" <nt_consulting64@yahoo.com> wrote

>> I have a nice application (in VB6) that uses
>> an ACCESS-Database. Now there must other
>> (always ready) existing applications use this
>> database and thatswhy it will converting into
>> a SYBASE-Database. My Problem is: how to
>> use the SYBASE-Database in my program ? (I've
>> never done bevore) Has anybody a "cooking-
>> instruction" or an exempel? (it is important for
>> me)
> Depends on the driver/providers you have available.
> http://www.connectionstrings.com/?carrier=sybase

If your tables are going to be identical, it's possible the simplest
approach would be to take a copy of the Access database you are using and
link the tables to the Sybase tables (using ODBC drivers for the Sybase DB)
and continue to use your application, with the Access database. That should
work just fine for either DAO or ADO, with minimal change required to your
application.

A well-designed, well-implemented single-user or multi-user application just
converted to client-server (as you'll be doing) with no tweaks is likely not
to be a well-designed, well-implemented client-server because of the
difference in the way file-server databases (like Jet, aka Access) operate
and the way server DBs (like all flavors of Sybase) operate. But, often,
the changes required are not a lot of work to implement, but do yield much
better response.

You can review presentations I did for my user group regarding Access in a
Multiuser Environment and Access as a Client, as well as one on Access or VB
as a front-end/client at http://appdevissues.tripod.com in the Downloads
section. The first two deal with Access, but many/most of the "issues" are
the same because it is a matter of using the DB, not so much the "front-end"
application generator.

Larry Linson
Microsoft Access MVP


"Ralph" <nt_consulting64@yahoo.com> wrote

>> I have a nice application (in VB6) that uses
>> an ACCESS-Database. Now there must other
>> (always ready) existing applications use this
>> database and thatswhy it will converting into
>> a SYBASE-Database. My Problem is: how to
>> use the SYBASE-Database in my program ? (I've
>> never done bevore) Has anybody a "cooking-
>> instruction" or an exempel? (it is important for
>> me)
> Depends on the driver/providers you have available.
> http://www.connectionstrings.com/?carrier=sybase

Dag Sunde schrieb:

>First, you need to determine what version of Sybase...
>Sybase have started developing their own ODBC driver
>for the later versions (12.x ++)...
>
>I will assume that you already have the Sybase ODBC drivers installed
>On your VB machine.
>
>I will also assume that you have not made the mistake of using VB's
>Bound data-controls, but use ADO directly. (NOT DAO).
>
>Here's how to make a connection to the db:
>
> Dim sConn As String
> sConn = "Provider=Sybase.ASEOLEDBProvider.2;"
> sConn = sConn & "Server Name192.168.1.50;" 'Machine or IP
> sConn = sConn & "Server Port Address=5000;"
> sConn = sConn & "Initial Catalog=DataBaseName;"
> sConn = sConn & "User ID=username;"
> sConn = sConn & "Password=myPassword;"
> sConn = sConn & "Persist Security Info=True;"
>
> Dim cn As ADODB.Connection
> Set cn = New ADODB.Connection
> cn.Open sConn
>
>From here on, the rest is excactly like before:
>
> Dim rs as ADODB.RecordSet
> Dim sql as string
>
> sql = "SELECT Name, Phone FROM Customers"
> Set rs = cn.Execute(sql)
> While Not rs.EOF
> Debug.Print rs("Name") & vbTab & rs("Phone")
> rs.MoveNext
> Wend
> rs.Close
> ...
> cn.Close
> Set rs = Nothing
> Set cn = Nothing
>
>Be aware of:
>Sybase TransactSQL is Case sensitive on Table/Procedure/Column names
>Sybase TSQL differs quite radically from Access SQL in some areas.
>
>(So check all your queries).
>
>HTH...
>
>

Thank You Dag,

I have done so (there ware many thousands lines of source-code) and now
the program is working with the sybase database.
One problem is left: how can I get the right charset? To convert the
datas, I read the access-database and insert into the sybase-database
(it is a sepaprate program). In this step I want to convert the
characters but i do not know witch is the right:
The line:
cn.Execute ("set char_convert cp850 ")
brings on result. So I must see witch charset is the right.

It has been a lot of work but now (i think) it is ready.

Thank you very much again
Uwe

Dag Sunde schrieb:

>First, you need to determine what version of Sybase...
>Sybase have started developing their own ODBC driver
>for the later versions (12.x ++)...
>
>I will assume that you already have the Sybase ODBC drivers installed
>On your VB machine.
>
>I will also assume that you have not made the mistake of using VB's
>Bound data-controls, but use ADO directly. (NOT DAO).
>
>Here's how to make a connection to the db:
>
> Dim sConn As String
> sConn = "Provider=Sybase.ASEOLEDBProvider.2;"
> sConn = sConn & "Server Name192.168.1.50;" 'Machine or IP
> sConn = sConn & "Server Port Address=5000;"
> sConn = sConn & "Initial Catalog=DataBaseName;"
> sConn = sConn & "User ID=username;"
> sConn = sConn & "Password=myPassword;"
> sConn = sConn & "Persist Security Info=True;"
>
> Dim cn As ADODB.Connection
> Set cn = New ADODB.Connection
> cn.Open sConn
>
>From here on, the rest is excactly like before:
>
> Dim rs as ADODB.RecordSet
> Dim sql as string
>
> sql = "SELECT Name, Phone FROM Customers"
> Set rs = cn.Execute(sql)
> While Not rs.EOF
> Debug.Print rs("Name") & vbTab & rs("Phone")
> rs.MoveNext
> Wend
> rs.Close
> ...
> cn.Close
> Set rs = Nothing
> Set cn = Nothing
>
>Be aware of:
>Sybase TransactSQL is Case sensitive on Table/Procedure/Column names
>Sybase TSQL differs quite radically from Access SQL in some areas.
>
>(So check all your queries).
>
>HTH...
>
>

Thank You Dag,

I have done so (there ware many thousands lines of source-code) and now
the program is working with the sybase database.
One problem is left: how can I get the right charset? To convert the
datas, I read the access-database and insert into the sybase-database
(it is a sepaprate program). In this step I want to convert the
characters but i do not know witch is the right:
The line:
cn.Execute ("set char_convert cp850 ")
brings on result. So I must see witch charset is the right.

It has been a lot of work but now (i think) it is ready.

Thank you very much again
Uwe

Uwe Gutsche wrote:
> Dag Sunde schrieb:
>
>> First, you need to determine what version of Sybase...
>> Sybase have started developing their own ODBC driver
>> for the later versions (12.x ++)...
<snipped/>
>> HTH...
>
> Thank You Dag,
>
> I have done so (there ware many thousands lines of source-code) and
> now the program is working with the sybase database.
> One problem is left: how can I get the right charset? To convert the
> datas, I read the access-database and insert into the sybase-database
> (it is a sepaprate program). In this step I want to convert the
> characters but i do not know witch is the right:
> The line:
> cn.Execute ("set char_convert cp850 ")
> brings on result. So I must see witch charset is the right.
>
> It has been a lot of work but now (i think) it is ready.
>
> Thank you very much again

Nice to hear you got it working...

When it comes to charsets, it is difficult for me to guess what
a (German?) Access DB will do, and what default your ASE is set up with.

My only advice is to make a small test-db, with a subset of your data,
and do some experimentation until you get it right.

Good luck!

--
Dag.