I am trying to insert & update records from Excel (visual basic) into a postgres table but am having problems connecting to postgres.
I am able to build querries in Excel no problem.
The postgres documentation give the following example to connect...
'Open the connection
cn.Open "DSN=<MyDataSourceName>;" & _
"UID=<MyUsername>;" & _
"PWD=<MyPassword>;" & _
"Database=<MyDatabaseName>"
I assume that the UID, PWD and Database are the same as the ones I would use in setting up the data source queries. the DSN is where I seem to fall down..
when I set up the datasource queries I specify "localhost" as the Server and 5432 as the Port
so I have tried using localhost:5432 as the DSN; so my code looks like:
Sub UpdateRecord()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
'Open the connection
cnn.Open "DSN=localhost:5432;" & _
"UID=postgres;" & _
"PWD=password;" & _
"Database=postgres"
End Sub
as the DSN but get
"Run Time Error 91"
"Object variable or With block variable not set
any ideas on what I am doing wrong??
Your DSN is wrong. But you knew that. You are using the ODBC driver and the DSN refers to the name you registered your PostgreSQL data source in ODBC manager (not the PostgreSQL server).
ReplyDeleteSo if you registered it as MyLocalPgServer, then that would be what you use for DSN
Did you try connecting to the Postgres database via Excel's get external data, data source GUI and/or, as Regina commented, creating a datasource that connects to the database? I haven't used the VBA approach but I've used the former to get PostgreSQL query results into Excel for charting. I haven't used VBA because there's a lot more functionality available in PostgreSQL queries and procedures.
ReplyDeleteFor whatever weird reason, your
ReplyDelete"PWD=password;"
line got turned into a hyperlink with the contents
mailto:PWD=!Q@aw3se4
Ah...the @ sign. Microsoft copy and paste? Or some other?
I have solved the Excel to Postgres using VB connection problem.
ReplyDeleteMS query is fine for doing reads from your database but it is sometimes usefull to write records from excel.
Here for others to use is the macro..
Sub UpdateRecord()
Dim cnn As New ADODB.connection
Dim rst As New ADODB.Recordset
'dim cnn.ConnectionString string
cnn.Open "Driver={PostgreSQL ODBC Driver(Unicode)};DSN=postgres;Server=localhost;Port=5432;UID=postgres;PWD=password; Database=postgres;" & _
"READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=1"
'dropTable = "DROP TABLE IF EXISTS nyse.blobtable"
'createTable = "CREATE TABLE nyse.blobtable (id INTEGER , myblob char(2), myBlobName VARCHAR(300))"
until
insertTable = "INSERT INTO nyse.customer" & _
" VALUES (customer.number,customer.name,customer.addr1,customer.addr2,customer.city,customer.postalcode,customer.country)"
' Execute queries and prepare blob writing
dropResult = cnn.Execute(dropTable)
createResult = cnn.Execute(createTable)
insertResult = cnn.Execute(insertTable)
cnn.Close
End Sub
I have used the add and drop table commands just to illustrate that you can do almost anything in the database from excel.
Harrah's Cherokee Casino & Hotel - Mapyro
ReplyDeleteHarrah's Cherokee Casino & Hotel. 0.2 mi (0.3 km) 양산 출장샵 from McCarran International 고양 출장샵 Airport. 3.8 mi (4 km) from Harrah's Cherokee Casino. 2.2 동해 출장안마 mi 보령 출장마사지 (1 km) from 제주도 출장마사지