IConnectCode

Convert String to Mixed Case

1:     Convert String to Upper/Lower Case: 

2:     <%

3:     Function PCase(strInput)

4:     

5:     Dim iPosition  

6:     Dim iSpace    

7:     Dim strOutput 

8:     

9:     iPosition = 1

10:       

11:    Do While InStr(iPosition, strInput, " ", 1) <> 0

12:     iSpace = InStr(iPosition, strInput, " ", 1)   

13:     strOutput = strOutput & UCase(Mid(strInput, iPosition, 1))

14:     strOutput = strOutput & 

15:     LCase(Mid(strInput, iPosition + 1, iSpace - iPosition))

16:     iPosition = iSpace + 1

17:    Loop

18:     strOutput = strOutput & UCase(Mid(strInput, iPosition, 1))

19:     strOutput = strOutput & LCase(Mid(strInput, iPosition + 1))

20:     PCase = strOutput

21:    End Function

22:    %> 

Read Only to Read Write!

Change a file from Read Only to Read - Write. Great if you put an mdb on a CD user doesn't have to know how to make it read/write when copied to his drive.

1:     Private Sub Form_Load()

2:     Dim FSO

3:     Dim ATT

4:     Set FSO = CreateObject("Scripting.FileSystemObject")

5:     Set ATT = FSO.GetFile(Me.Application.CurrentProject.Path &

6:     "\UPS_DATA_CREATER.mdb")

7:     

8:     ATT.Attributes = 0

9:     End Sub

10:    where "\UPS_DATA_CREATER.mdb" = to application name

Refresh Links

This code snippet can be used to refresh table links in an Access or VB application by looping through the ADO Catalog to find tables, determine if they are linked in the application, and relinking if they are.

1:     Sub RefreshLinks()

2:         Dim cat As ADOX.Catalog

3:         Dim tbl As ADOX.Table

4:        Set cat = New ADOX.Catalog

5:        cat.ActiveConnection = CurrentProject.Connection

6:      Set tbl = New ADOX.Table

7:      For Each tbl In cat.Tables

8:        If tbl.Type = "LINK" Then

9:          tbl.Properties("Jet OLEDB:Link Provider String") 

10:        = "MS Access;PWD=Admin;"

11:        End If

12:     Next

13:     End Sub

Loop thru all the Tables in a database Without Knowing the Names

1:     loop thru all the tables in a database without knowing the names." 

2:     

3:     

4:     dim oConn, oRSTbl

5:     

6:     '

7:     ' Open the object that we'll need.

8:     '

9:     set oConn = CreateObject ("ADODB.Connection")

10:    set oRSTbl = CreateObject ("ADODB.Recordset")

11:    oConn.open "DSN=MyDSN;

12:    

13:    '

14:    ' Pass 20 to the function for Tables.

15:    '

16:    set oRSTbl = oConn.OpenSchema (20)

17:    

18:    '

19:    ' Fly down our schema and generate a message

20:    ' for each table we find

21:    '

22:    while not oRSTbl.eof

23:        MsgBox "Table is [" + oRSTbl ("TABLE_NAME") + "]"

24:        oRSTbl.movenext

25:    wend

26:    

27:    

Loop Thru All the Sites on an IIS Server

1:     loop thru all the sites on an IIS server." 

2:     Dim IISOBJ, WebSite

3:     Set IISOBJ = GetObject("IIS://Localhost/W3SVC")

4:     For each WebSite in IISOBJ

5:       if (WebSite.Class = "IIsWebServer") then

6:         MsgBox WebSite.ServerComment + "("_

7:           + GetPath (WebSite) + ")"

8:       end if

9:     Next

10:    

11:    Function GetPath (WebSite)

12:    Dim Root, NewADSPath, Path

13:      Set Root = GetObject(WebSite.ADSPath & "/ROOT")

14:      GetPath = lcase(Root.Path)

15:    End Function

16:    

17:    

VB DB2 Connection - Client

db2Connection.ConnectionString = "Provider-MSDASQL.1;Persist Security Info=False;Data Source=DSNAS400DB2;Mode=ReadWrite"