By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,762 Members | 1,838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,762 IT Pros & Developers. It's quick & easy.

Change Column definitions?

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"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

' ************************************************** **********************
Nov 12 '05 #2

P: n/a
thanks Fletcher, thats just what I wanted to know. Can you or anyone else
recommend a text book on JET v4 for access 2000.

Tim

"Fletcher Arnold" <fl****@home.com> wrote in message
news:bm**********@sparta.btinternet.com...
"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

' ************************************************** **********************

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.