Adding fields to multiple tables via code

Adding fields to multiple tables via code

am 06.07.2005 14:00:39 von Steven Burn

The short version:

I've got 3000+ databases on one of my servers that I am planning on updating
(couple new fields etc) and am wondering about the best way of doing this
without doing it manually.

Unfortunately the server is owned by FastHosts so using my own components is
unfortunately, out of the question.

The server is Windows Server 2003 and the databases are all MS Access 2000.

I was basically thinking of using FSO combined with bog standard Jet code
(FH don't allow ADOX for some reason)), something along the lines of;

Code:
<%
'// UpdateDB.asp
Dim objFSO, objFldr, objSFldr, objDB, strFolderPath, blnResponse
'// main DB folder
strFolderPath = "../private/db/"
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
Set objFldr = objFSO.GetFolder(Server.MapPath(strFolderPath))

'// Loop through the databases
For Each objSFldr in objFldr.SubFolder
objDB = objSFldr.Path & "\" & objSFldr.Name & "db.mdb"
blnResponse = AddField(objDB,

, )
'// Some response.writes to verify blnResponse
Next
'// Cleanup
Set objDB = Nothing
Set objFldr = Nothing
Set objFSO = Nothing

Public Function AddField(sDBPath, tTable, sNewField, sType)
'// This function is a modified version of code taken from:
'// http://www.visualbasicforum.com/t16324.html

Dim cnnJet As ADODB.Connection, strDBName As String
'// create a connection string with the proper path
strDBName = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source" + sDBPath
+ ";Mode=Read|Write"
'// Open up the ADODB Connection
Set cnnJet = New ADODB.Connection
On Error Resume Next
With cnnJet
.ConnectionString = strDBName
.Open
'// here you can allow for different data types
Select Case LCase(sType)
Case "text"
.Execute "Alter Table " & tTable & " ADD COLUMN " &
sNewField & " TEXT(255)"
Case "memo"
.Execute "Alter Table " & tTable & " ADD COLUMN " &
sNewField & " MEMO"
Case "numeric"
.Execute "Alter Table " & tTable & " ADD COLUMN " &
sNewField & " INTEGER"
Case "percent"
.Execute "Alter Table " & tTable & " ADD COLUMN " &
sNewField & " NUMERIC(5,2)"
End Select
.Close
End With
Set cnnJet = Nothing
AddField = True
Exit Function
BadData:
cnnJet.Close
Set cnnJet = Nothing
End Function
%>

My question's are;

1. Would this work without causing problems?
2. As ADOX is not allowed, what would be the best way of inserting the
fields into the database?

Thankyou in advance for any replies.

Re: Adding fields to multiple tables via code

am 06.07.2005 15:00:43 von reb01501

Steven Burn wrote:
> The short version:
>
> I've got 3000+ databases on one of my servers that I am planning on
> updating (couple new fields etc) and am wondering about the best way
> of doing this without doing it manually.
>

> .Execute "Alter Table " & tTable & " ADD COLUMN "
> & sNewField & " NUMERIC(5,2)"

> My question's are;
>
> 1. Would this work without causing problems?.

Sure, why not? I presume you've tested it on your own server ...

> 2. As ADOX is not allowed, what would be the best way of inserting the
> fields into the database?
>
There's no alternative: you have to use JetSQL. Sure, you could create a VB
app that uses DAO ...

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Adding fields to multiple tables via code

am 06.07.2005 18:04:55 von Steven Burn

"Bob Barrows [MVP]" wrote in message
news:#$HH5qigFHA.572@TK2MSFTNGP15.phx.gbl...
> Steven Burn wrote:
> > The short version:
> >
> > I've got 3000+ databases on one of my servers that I am planning on
> > updating (couple new fields etc) and am wondering about the best way
> > of doing this without doing it manually.
> >
>
> > .Execute "Alter Table " & tTable & " ADD COLUMN "
> > & sNewField & " NUMERIC(5,2)"
>
> > My question's are;
> >
> > 1. Would this work without causing problems?.
>
> Sure, why not? I presume you've tested it on your own server ...
>

hehe, cheers Bob (can't test it on my server till I get a chance to backup
the files)

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

Re: Adding fields to multiple tables via code

am 10.07.2005 20:03:27 von Steven Burn

Just an FYI..... I finally got round to testing the code and besides a minor
niggle (forgot to remove part of the VB code), it worked perfectly. Incase
anyone else is ever in a need to do the same, I've posted the code below and
will post it on devdex later.

'// BEGIN
<%
'// UpdateDB.asp
'// Requires:
'//
'// Field names (line 22/23)
'// Tables names (line 22/23)
'// Original DB folder Path (line 12)
'// Backup folder path (line 17)

Server.ScriptTimeout = 500
Dim objFSO, objFldr, objSFldr, objDB, strFolderPath, blnResponse
'// main DB folder
strFolderPath = "../private/db/"
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
Set objFldr = objFSO.GetFolder(Server.MapPath(strFolderPath))

'// Backup the originals... just incase something goes wrong
objFSO.CopyFolder objFldr.path, Server.MapPath("../private/db_backup/") &
objFldr.Name

'// Loop through the databases
For Each objSFldr in objFldr.SubFolders
objDB = objFldr.Path & "\" & objSFldr.Name & "\db.mdb"
blnResponse = AddField(objDB, , , )
Response.Write objSFldr.Name & " : Field: " & & " - " &
blnResponse & "
"
Next
Response.Write "Databases updated"
'// Cleanup
Set objDB = Nothing
Set objFldr = Nothing
Set objFSO = Nothing

Public Function AddField(sDBPath, tTable, sNewField, sType)
'// This function is a modified version of code taken from:
'// http://www.visualbasicforum.com/t16324.html

Dim cnnJet, strDBName
'// create a connection string with the proper path
strDBName = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sDBPath
'// Open up the ADODB Connection
Set cnnJet = Server.CreateObject("ADODB.Connection")
With cnnJet
.ConnectionString = strDBName
.Mode = adModeReadWrite
.Open
'// here you can allow for different data types
On Error Resume Next
Select Case LCase(sType)
Case "text"
.Execute "Alter Table " & tTable & " ADD COLUMN " & sNewField & "
TEXT(255)"
Case "memo"
.Execute "Alter Table " & tTable & " ADD COLUMN " & sNewField & " MEMO"
Case "numeric"
.Execute "Alter Table " & tTable & " ADD COLUMN " & sNewField & "
INTEGER"
Case "percent"
.Execute "Alter Table " & tTable & " ADD COLUMN " & sNewField & "
NUMERIC(5,2)"
Case "yesno"
.Execute "Alter Table " & tTable & " ADD COLUMN " & sNewField & " YESNO"
End Select
.Close
End With
Set cnnJet = Nothing
AddField = True
End Function
%>
'// END