| Ronald Oostdam 2005-08-23, 7:02 pm |
| There is probably a "Relation" to an other table or an "Index" on some
keyfields in Table1...
"JD" <jdkunf@mbiri.com> wrote in message
news:%23iEb1eAqFHA.2416@TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm using VB6 and Access 2000. I'm running an ALTER TABLE SQL statement
> to change a primary key on a column and datatypes on other columns. I'm
> using the code:
>
> Dim c as New ADODB.Connection
> c.CurserLocation = adUseClient
> c.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=C:\MyDB.mdb;Persist Security Info=False"
>
> c.Execute "ALTER TABLE Table1 ALTER COLUMN ID long primary key"
> c.Execute "ALTER TABLE Table1 ALTER COLUMN Name text(30) not null"
> c.Execute "ALTER TABLE Table1 ALTER COLUMN Address text(50) not null"
> c.Execute "ALTER TABLE Table1 ALTER COLUMN Address text(50) not null"
> c.Execute "ALTER TABLE Table1 ALTER COLUMN City text(50) not null"
> c.Execute "ALTER TABLE Table1 ALTER COLUMN State text(2) not null"
> c.Execute "ALTER TABLE Table1 ALTER COLUMN ZipCode text(5) not null"
>
> I am getting errors that the primary key already exists. I added the code
> between the *********:
>
> ********************************
> On Error GoTo LocErr
> ********************************
>
> c.Execute "ALTER TABLE Table1 ALTER COLUMN ID long primary key"
> c.Execute "ALTER TABLE Table1 ALTER COLUMN Name text(30) not null"
> c.Execute "ALTER TABLE Table1 ALTER COLUMN Address text(50) not null"
> c.Execute "ALTER TABLE Table1 ALTER COLUMN Address text(50) not null"
> c.Execute "ALTER TABLE Table1 ALTER COLUMN City text(50) not null"
> c.Execute "ALTER TABLE Table1 ALTER COLUMN State text(2) not null"
> c.Execute "ALTER TABLE Table1 ALTER COLUMN ZipCode text(5) not null"
>
> ********************************
> This ends the script without any error messages but doesnt change the
> datatypes.
> LocErr:
> If Err.Number = -2147467259 then Resume Next
> MsgBox Err.Number & Err.Description
> Exit Sub
> ********************************
>
>
> How can I check each column to see if it if it correct then move to the
> next column without displaying the error messages and ending the script
> without recreating the database.
>
>
> Thanks in advance
>
> J
>
|