Author |
|
MrGibbage Super User
Joined: October 23 2006 Location: United States
Online Status: Offline Posts: 513
|
Posted: January 28 2010 at 15:11 | IP Logged
|
|
|
It looks like the real Power Programmers here like to use
the direct SQL commands to get things done. Sounds like a
good idea to me, and I think it will simplify a lot of my
macros. Is there a list of tables somewhere that PH uses?
I think I can figure out what to do from there.
|
Back to Top |
|
|
grif091 Super User
Joined: March 26 2008 Location: United States
Online Status: Offline Posts: 1357
|
Posted: January 28 2010 at 19:00 | IP Logged
|
|
|
The following link to a very old post may provide some of the information you are looking for. I am afraid that what you attribute to Power Programmers is really just the export of a Macro definition.
Powerhome Database
__________________ Lee G
|
Back to Top |
|
|
device Newbie
Joined: May 26 2009
Online Status: Offline Posts: 33
|
Posted: February 01 2010 at 16:29 | IP Logged
|
|
|
Here is the first VBScript I ever wrote which will answer your question. It writes all tables, columns and column types out to a file. I have stuck with VBScript to do all the progammatic work because the environment while new to me then was more familiar. Maybe you will find this useful in enumerating all tables and columns for SQL manipulation.
D
Function enumtables()
On Error Resume Next
enumtables = "failed"
Set fso = CreateObject("Scripting.FileSystemObject")
Set tableinfo = fso.CreateTextFile("c:\Program Files\powerhome\tables.txt", True)
Set fso = Nothing
sysstatus = ph.sqlselect(1, "SELECT * FROM sys.syscatalog")
If (sysstatus = 0) Then
enumtables = "success"
tablecount = ph.getsqlrows(1)
For tableindex = 1 To tablecount
tablename = ph.getdata_cn(1, CLng(tableindex), "tname")
If tablename <> "SYSUSERPERM" Then
tablestatus = ph.sqlselect(2, "SELECT * FROM " & tablename)
tableinfo.WriteLine("Table : " & tablename)
If (tablestatus = 0 Or tablestatus = 2) Then
colcount = ph.getcolcount(2)
For colindex = 1 To colcount
colname = ph.getcolname(2, CLng(colindex))
coltype = ph.getcoltype(2, Clng(colindex))
tableinfo.WriteLine(" " & CStr(colindex) & " : " & colname & " : " & coltype)
Next
Else
enumtables = "success with errors"
tableinfo.WriteLine(" &nb sp; Error " & CStr(tablestatus) & " opening " & tablename)
End If
tableinfo.WriteBlankLines(3)
ph.sqldestroy(2)
End If
Next
ph.sqldestroy(1)
End If
tableinfo.Close
Set tableinfo = Nothing
End Function
|
Back to Top |
|
|
device Newbie
Joined: May 26 2009
Online Status: Offline Posts: 33
|
Posted: February 01 2010 at 16:32 | IP Logged
|
|
|
A "&nb sp;" snuck in there when the message was converted to HTML which should be removed.
D
|
Back to Top |
|
|
MrGibbage Super User
Joined: October 23 2006 Location: United States
Online Status: Offline Posts: 513
|
Posted: February 02 2010 at 09:40 | IP Logged
|
|
|
I have connected to the PH database with Access over ODBC.
Wow, I had no idea there were so many tables! I now have
the information I need to start playing.
You script does look useful. I think I will be able to use
it in other situations like this as well (outside of PH).
|
Back to Top |
|
|
device Newbie
Joined: May 26 2009
Online Status: Offline Posts: 33
|
Posted: February 02 2010 at 13:59 | IP Logged
|
|
|
Actually the VBScript assumes it is running in PH. I wrote this a while back to begin to become familiar with VBScript, SQL and PH. I do a fair amount of SQL using VBScript in servicing PH events. If you need to manipulate the tables outside of PH and you have and are familiar with Access then that probably is the way to go. Have fun,
D
|
Back to Top |
|
|