"Tim Newton" <ti**@bigfoot.com> wrote in message
news:bm**********@sparta.btinternet.com...
Hi
I have a vb app that uses an access database to store information. This
app has been distributed to several users. I would like to increase the size
of a field in an access table using my vb app so the users dont have to input
all their data again, what is the best way to do it. I normally use oracle
and would call something like an "alter table modfiy column col1
varchar2(100)". I'm guessing this cannot be done in access and a different
approach is needed.
Thanks for any help,
Tim
http://newtonsoftware.net
Tim
If you are talking about Access 2000 onwards, ie version 4 of the Jet
database, then you can make use ALTER TABLE statement in the way you
suggest - well almost - change "varchar2(100)" to "TEXT(100)".
Here is a script which, although written in vb script, should show you the
idea.
HTH
Fletcher
' ************************************************** **********************
Const ROUTINE = "Update Routine"
Const DB_PATH = "C:\Test\"
Const STR_SQL = "ALTER TABLE MyTable ALTER COLUMN MyColumn TEXT(100)"
Call Main
Sub Main
Dim cnn
Dim strMsg
Dim strPath
strPath = GetPath()
If Len(strPath) = 0 Then
Msgbox "Update cancelled",vbInformation, ROUTINE
Exit Sub
End If
Set cnn = GetConnection(strPath)
If cnn Is Nothing Then
Msgbox "Unable to esatblish a Connection to database:" & vbCrLf & _
strPath & "'", vbCritical, ROUTINE
Exit Sub
End If
strMsg = TableError(cnn)
If Len(strMsg) = 0 Then
Msgbox "Table updated successfully", vbInformation, ROUTINE
Else
Msgbox "Error updating table:" & vbCrLf & strMsg, vbCritical, ROUTINE
End If
cnn.Close
Set cnn = Nothing
End Sub
Function GetPath()
On Error Resume Next
Dim objDlg
Dim str
str = ""
set objDlg = CreateObject("MSComDlg.CommonDialog")
If Err.Number = 0 Then
objDlg.Filter="Microsoft Access Databases (*..mdb)|*.mdb"
objDlg.DialogTitle = ROUTINE & " - Select the database..."
objDlg.InitDir = DB_PATH
objDlg.MaxFileSize = 260
objDlg.ShowOpen
objDlg.Flags = objDlg.Flags OR cdlOFNPathMustExist
objDlg.Flags = objDlg.Flags OR cdlOFNFileMustExist
objDlg.Flags = objDlg.Flags OR cdlOFNHideReadOnly
str = objDlg.FileName
End If
Set objDlg = nothing
GetPath = str
End Function
Function GetConnection(strPath)
On Error Resume Next
Dim cnn
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPath
Set cnn = CreateObject("ADODB.Connection")
cnn.Open strCnn
If Err.Number = 0 Then
Set GetConnection = cnn
Else
Set GetConnection = Nothing
End If
End Function
Function TableError(cnn)
On Error Resume Next
Dim strErrMsg
strErrMsg = "Cannot Update Table"
cnn.Execute STR_SQL
If Err.Number = 0 Then
strErrMsg = ""
Else
strErrMsg = Err.Description
End If
TableError = strErrMsg
End Function
' ************************************************** **********************
