HOW TO DO VACUUM IN VB FOR POSTGRES DATABASE - URGENT

Hi everyone,

I am using VB6, and PostGres Database. I would like to Vacuum the database by coding. Currently, I use PGADMIN II to do this task. I right click on the table and select Vacuum. How do I do this job by writing a VB code to do that...
neonlightsAsked:
Who is Participating?
 
Tommy KinardConnect With a Mentor Commented:
How are you connecting to the database? with ODBC is it installed on a linux/microsoft server? or is it on a windows desktop?
Why not put Vacuum in the SQL statement?

Public cn As rdoConnection
SQL = "vacuum"
cn.Execute SQL, rdExecDirect

that works for me with Redhat Linux  7.2 server, Postgres 6.5 from a windows98se PC, using ODBC.

HTH
dragontooth

0
 
Tommy KinardCommented:
Note this will eventually cause a problem if more than one user is connected to the database so it would actually be better to run the vacuum during the night when the data has a better chance of not getting corrupt.

In Linux I have a cron setup to run each night at 3:00 AM

Just a few thoughts
dragontooth

0
 
neonlightsAuthor Commented:
Thanks Drangontooth,

I am using all what you mentioned:

Redhat Linux  7.2 server, Postgres 6.5 from a windows98se PC, using ODBC.

This system is used by 10 or more people. But, there is a one controller (PC), it maintains the clean up. So, this controller can execute this statement? am I right?

Public cn As rdoConnection
SQL = "vacuum"
cn.Execute SQL, rdExecDirect

Can I use SQL word? and if I run this statement, will vacuum the database...I know it should,,,just wondering...
Thanks
Here is my points.
Do you know how to send wav file over phone line? if you know, I also post question on that. 250 points...let me know...
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
neonlightsAuthor Commented:
Right now, I do vacuum usining PGADMIN II, right click on the table, and do vacuum. So, if I do this statement, do I have to mention what table? or it will vacuum all the tables? how this works?

Thanks
0
 
Tommy KinardCommented:
Thanks for the points and the Grade!

The SQL is dim as a string cn.Execute SQL, rdExecDirect tells the ODBC to execute this command weather it is a SQL statement or a stored procedure or an internal command.

LOL no I dn't know how to send a wave file over a phone line, sorry.

In the ODBC driver (in the connection string) there is a field which you specify what database you are loging on to. All of the tables will be vacuumed in that database.

If you need more help let me know
dragontooth

0
 
neonlightsAuthor Commented:
You are welcome!.

So, if I do
Dim SQL as String
Public cn As rdoConnection
SQL = "vacuum"
cn.Execute SQL, rdExecDirect

do the job, and vacuum all the tables? wow,...cool

Thanks
0
 
Tommy KinardCommented:
This is a function I wrote to execute my SQLs etc.  The Conn is a boolean that I use to determine if the connection is active in which case I reuse the connection, otherwise I establish a connection.

I would suggest this:

Public SQL as String
Public cn As rdoConnection

Public Function ExecSql()
    On Error GoTo CnEh
    If Not Conn Then
            cn.CursorDriver = rdUseOdbc
'change the DSN to yours; you will also need to put in the database;server ip/name;userid(UID);password
            cn.Connect = "DSN=sched;DATABASE=" & Database & ";SERVER=10.0.0.1;PORT=5432;UID=;PWD=;READONLY=0;PROTOCOL=6.5;FAKEOIDINDEX=1;SHOWOIDCOLUMN=1;ROWVERSIONING=0;SHOWSYSTEMTABLES=1;CONNSETTINGS="
        cn.EstablishConnection
        Conn = True
    End If
    cn.Execute SQL, rdExecDirect
    cn.BeginTrans
    cn.CommitTrans
    On Error GoTo 0
    Exit Function
CnEh:
    Err.Clear
    On Error GoTo 0
End Function

dragontooth

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.