6 Replies - 10347 Views - Last Post: 22 November 2010 - 04:22 PM Rate Topic: -----

#1 sharpy  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 35
  • View blog
  • Posts: 394
  • Joined: 02-June 07

Add a new column to an existing Access table

Posted 20 November 2010 - 01:42 PM

Hi All

What I want to do is add a new column to an existing access table which I have already created using the code below.




'Define the connectors
        Dim oConn As OleDbConnection
        Dim oComm As OleDbCommand
        Dim oConnect, oQuery As String


        'Define connection string
        oConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\MFSProFilesACDB\Databases\Partdata.mdb"



        'Define the query string that creates the table for Partinformation access table and insert table

        oQuery = "CREATE TABLE PartInformatiom ( ID Counter," & _
        "PartNumber TEXT(50)," & _
        "Estimatenumber TEXT(50)," & _
        "Partname TEXT(100) ," & _
        "RevNumber TEXT(50) ," & _
        "Material TEXT(50)," & _
        "ProcessDescription TEXT(250)," & _
        "MachineType TEXT(50)," & _
        "NumberParts TEXT(50)," & _
        "NumberOpps TEXT(50)," & _
        "LoadTime TEXT(50)," & _
        "PRIMARY KEY(ID) )"
        ' Instantiate the connectors
        oConn = New OleDbConnection(oConnect)
        oComm = New OleDbCommand(oQuery, oConn)
        'Try connecting and create the table
        Try

            'Open the connection
            oConn.Open()

            'Perform the Non-Query
            oComm.ExecuteNonQuery()

            'Close the connection
            oConn.Close()

        Catch ex As OleDb.OleDbException
        Catch ex As Exception

            'Finally

            'Dispose the connector objects
            If Not (oConn Is Nothing) Then
                oConn.Dispose()
                oConn = Nothing
            End If

            If Not (oComm Is Nothing) Then
                oComm.Dispose()
                oComm = Nothing
            End If

        End Try



The reasoning behind this is if I want to add a column at a later date I can. Now I could just add spare columns now to use at a later date but I think that's a little bit untidy.

I have had a look around for some examples with no luck. If any body can point me in the right direction then it would be much appreciated.

Thanks in advance

Is This A Good Question/Topic? 0
  • +

Replies To: Add a new column to an existing Access table

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8371
  • View blog
  • Posts: 31,100
  • Joined: 12-June 08

Re: Add a new column to an existing Access table

Posted 21 November 2010 - 01:56 PM

You would want to look into the 'alter table' SQL command to add a column to a table in Access.

Check out here:
http://stackoverflow...-table-from-net

This post has been edited by modi123_1: 21 November 2010 - 01:56 PM

Was This Post Helpful? 0
  • +
  • -

#3 Guest_tvanderv*


Reputation:

Re: Add a new column to an existing Access table

Posted 21 November 2010 - 01:58 PM

View Postsharpy, on 20 November 2010 - 12:42 PM, said:

Hi All

What I want to do is add a new column to an existing access table which I have already created using the code below.




'Define the connectors
        Dim oConn As OleDbConnection
        Dim oComm As OleDbCommand
        Dim oConnect, oQuery As String


        'Define connection string
        oConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\MFSProFilesACDB\Databases\Partdata.mdb"



        'Define the query string that creates the table for Partinformation access table and insert table

        oQuery = "CREATE TABLE PartInformatiom ( ID Counter," & _
        "PartNumber TEXT(50)," & _
        "Estimatenumber TEXT(50)," & _
        "Partname TEXT(100) ," & _
        "RevNumber TEXT(50) ," & _
        "Material TEXT(50)," & _
        "ProcessDescription TEXT(250)," & _
        "MachineType TEXT(50)," & _
        "NumberParts TEXT(50)," & _
        "NumberOpps TEXT(50)," & _
        "LoadTime TEXT(50)," & _
        "PRIMARY KEY(ID) )"
        ' Instantiate the connectors
        oConn = New OleDbConnection(oConnect)
        oComm = New OleDbCommand(oQuery, oConn)
        'Try connecting and create the table
        Try

            'Open the connection
            oConn.Open()

            'Perform the Non-Query
            oComm.ExecuteNonQuery()

            'Close the connection
            oConn.Close()

        Catch ex As OleDb.OleDbException
        Catch ex As Exception

            'Finally

            'Dispose the connector objects
            If Not (oConn Is Nothing) Then
                oConn.Dispose()
                oConn = Nothing
            End If

            If Not (oComm Is Nothing) Then
                oComm.Dispose()
                oComm = Nothing
            End If

        End Try



The reasoning behind this is if I want to add a column at a later date I can. Now I could just add spare columns now to use at a later date but I think that's a little bit untidy.

I have had a look around for some examples with no luck. If any body can point me in the right direction then it would be much appreciated.

Thanks in advance


hmm id read somewhere that you cant use the CREATE TABLE command in acces.
Was This Post Helpful? 0

#4 ahabich  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 18
  • Joined: 14-November 10

Re: Add a new column to an existing Access table

Posted 21 November 2010 - 07:57 PM

Try this:

oQuery = "ALTER TABLE PartInformatiom ADD COLUMN MyCol TEXT(50)"
Was This Post Helpful? 1
  • +
  • -

#5 sharpy  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 35
  • View blog
  • Posts: 394
  • Joined: 02-June 07

Re: Add a new column to an existing Access table

Posted 22 November 2010 - 12:10 PM

Thanks to all for the response. I've got plenty to go on now.

will post the code when I've got it working


Cheers

Sharpy
Was This Post Helpful? 0
  • +
  • -

#6 sharpy  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 35
  • View blog
  • Posts: 394
  • Joined: 02-June 07

Re: Add a new column to an existing Access table

Posted 22 November 2010 - 12:27 PM

Thanks Ahabich

Its simple when you know how. Code below





'Open Access Partdata database & add column to table material
        'Define connection string
        oConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\MFSProFilesACDB\Databases\Partdata.mdb"

        oQuery = "ALTER TABLE MaterialData ADD COLUMN TYPE TEXT(50)"
        
        ' Instantiate the connectors
        oConn = New OleDbConnection(oConnect)
        oComm = New OleDbCommand(oQuery, oConn)
        'Try connecting and create the table
        Try

            'Open the connection
            oConn.Open()

            'Perform the Non-Query
            oComm.ExecuteNonQuery()

            'Close the connection
            oConn.Close()

        Catch ex As OleDb.OleDbException
        Catch ex As Exception

            'Finally

            'Dispose the connector objects
            If Not (oConn Is Nothing) Then
                oConn.Dispose()
                oConn = Nothing
            End If

            If Not (oComm Is Nothing) Then
                oComm.Dispose()
                oComm = Nothing
            End If

        End Try


Cheers to all

Sharpy :bigsmile:
Was This Post Helpful? 0
  • +
  • -

#7 ahabich  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 18
  • Joined: 14-November 10

Re: Add a new column to an existing Access table

Posted 22 November 2010 - 04:22 PM

You are welcome.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1