connect to postgresql and get tables

I am trying to connect to postgresql from visual basic 6. I have both programs installed on my pc which means that the server is localhost. I have tried:

cn.Open "DSN=PostgreSQL;" & _
"UID=postgres;" & _
"PWD=***;" & _
"Database=mydb"

but it does not work. I gives me a runtime error: -2147467259 in vb6.

I have also tried this:
Const strPG As String = "Driver={PostgreSQL ANSI}; Server=localhost; PORT=5432; Database=mydb; UID=postgres; PWD=***"

but i don't know if it works, because I'm not able to get further, because I don't know how to define the records.

I have to mention that i have allready made a 400+ pages program which uses a lot of data from Access, so I'm quite familiar with those commands.

Any help would be much apprecialted!

welcome to the forums. i personally wouldnt use a DSN as you would then have to configure every computer that has the app. i would personally use ADO.

first, make a reference to Microsoft AxtiveX Data Objects 2.0 and then search the forums for connecting using ADO, itll be so much easier doing it this way. all you then have to do is code your connection string and you wont have to configure every computer to use the app.

Quote:

---

Originally Posted by JonasBruhn

I am trying to connect to postgresql from visual basic 6. I have both programs installed on my pc which means that the server is localhost. I have tried:

cn.Open "DSN=PostgreSQL;" & _
"UID=postgres;" & _
"PWD=***;" & _
"Database=mydb"

but it does not work. I gives me a runtime error: -2147467259 in vb6.

I have also tried this:
Const strPG As String = "Driver={PostgreSQL ANSI}; Server=localhost; PORT=5432; Database=mydb; UID=postgres; PWD=***"

but i don't know if it works, because I'm not able to get further, because I don't know how to define the records.

I have to mention that i have allready made a 400+ pages program which uses a lot of data from Access, so I'm quite familiar with those commands.

Any help would be much apprecialted!

---

Try this code. I don't use Postgres so I can't garantee that it works. Make sure that you have the Postgres ODBC driver installed.

VB Code:

Dim myConnection As New ADODB.Connection 
myConnection.open "DRIVER={PostgreSQL};SERVER=localhost;port=5432;DATABASE=yourDatabase;UID=yourUserID;PWD=yourPassword;"

Something else you might try is to create a DSN and I believe that somewhere in the process you have the option to test the connection. If the test passes then you can save your DSN and then go back and edit the configuration. You should at that point be able to see the connection string that is being used and you can then copy that into your VB program.

thx for all the replies, but I have solved the problem now. If any has the same problem the solution that worked for me came in two steps. The first step is that I used this link to setup the ADODB:

http://www.mygnet.com/articulos/vb/72/

The language is one that I don't know, but I could use the illustrations - gif.

The secont step was to adjust my vb code to this:

Private Sub command1_click()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection

cn.Open "Provider=PostgreSQL.1;Password=***;User ID=postgres;Data Source=localhost;Location=mydb" ';Extended Properties="""

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "Select * from weather", cn, adOpenStatic, adLockReadOnly
Set rs.ActiveConnection = Nothing

Set DataGrid1.DataSource = rs
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

I have not used any port, because both vb and postgresql is located on my computer and I do therefore for now not need any port to tunneling the connection.

Thx. again for the replies and I hope that others can use this method