[sharpy]

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

[modi123_1]

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

[ahabich]

Try this:

oQuery = "ALTER TABLE PartInformatiom ADD COLUMN MyCol TEXT(50)"

[sharpy]

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

[sharpy]

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

[ahabich]

You are welcome.