Access - VBA Alter a field in table - settings error

how are you running the "Alter table... ?

currentdb.execute "ALTER TABLE RAMP2015 ALTER COLUMN RAMP2015Company TEXT(50);"

Yes.

Application.CurrentDb.Execute "ALTER TABLE RAMP2015 ALTER COLUMN RAMP2015Company TEXT(50);"

Select allOpen in new window

It is very odd, because if I go to the imported table without using VBA and just modify the dropdown from Memo to Text the query executes without error. Shouldn't the VBA essentially be doing the same thing?

If I run the VBA and then modify the field from Text back to Memo and then back to Text again manually it also allows me to run the table query without error.

I am pulling my hair out with this :(

Adam

try doing a compact and repair (if you haven't done so) then run the code.

btw. when do you run the codes you posted ?

also, in the VBA window do DEBUG>Compile
make sure it compiles without error

It is part of a larger VBA macro that imports a few different SharePoint tables (based on a Saved import),  This happens when button on a form is clicked.

Private Sub RAMPData_Click()
DoCmd.SetWarnings False

'Remove the current data
DoCmd.DeleteObject acTable, "RAMP2015"
DoCmd.DeleteObject acTable, "RAMP2015Mitigations"
DoCmd.DeleteObject acTable, "RAMP2015QuarterlyUpdates"
DoCmd.DeleteObject acTable, "_Products"

'Import RAMP Tables
DoCmd.RunSavedImportExport "RAMP_ALL"
DoCmd.RunSavedImportExport "Products"

Me.CurrentDate.Caption = "Updated On " & Date

'Change Data Type for Memo to Text for Repport Purposes
Application.CurrentDb.Execute "ALTER TABLE RAMP2015 ALTER COLUMN RAMP2015Company TEXT(50);"
Application.CurrentDb.Execute "ALTER TABLE RAMP2015 ALTER COLUMN RAMP2015BusinessOwner TEXT;"
Application.CurrentDb.Execute "ALTER TABLE RAMP2015 ALTER COLUMN RAMP2015ActivityNature TEXT;"
Application.CurrentDb.Execute "ALTER TABLE RAMP2015 ALTER COLUMN RAMP2015Interaction TEXT;"
Application.CurrentDb.Execute "ALTER TABLE RAMP2015 ALTER COLUMN RAMP2015TherapeuticArea TEXT;"
Application.CurrentDb.Execute "ALTER TABLE RAMP2015 ALTER COLUMN RAMP2015TransactionType TEXT;"
Application.CurrentDb.Execute "ALTER TABLE RAMP2015Mitigations ALTER COLUMN MitigationRAMPParent TEXT;"
Application.CurrentDb.Execute "ALTER TABLE RAMP2015QuarterlyUpdates ALTER COLUMN QuarterlyUpdateRAMPParent TEXT;"

'Script Complete
MsgBox ("RAMP Data Updated")

DoCmd.SetWarnings True
End Sub

Select allOpen in new window

Just to be clear - I tried commenting out all of the Alter statements except the first one to see what I could modify to prevent the error.

Also - when I only do the Alter for one field the error only happens once and then the query executes fine.

I was not able to execute the Compile function- it was grayed out.

Still not sure why the error doesn't happen when I modify the data type manually. Wish there was a way to record a macro in Access to see if there is something I am missing.

before you run the "alter table statement..

place this code

RefreshDatabaseWindow

' run your alter tables here

I added it to the VBA and the results were the same.  I thought maybe it needed to be
Application.RefreshDatabaseWindow - but that didn't correct the issue either.

:-(

Thank you for your help so far... anything else I can try? Maybe just making a completely new copy of the table and force all the fields to TEXT? What would that code look like?

1. why do you need to delete the tables?
   what if you just delete the contents of the table..
   so instead of

          DoCmd.DeleteObject acTable, "RAMP2015"

          use

          currentdb.execute "delete * from RAMP2015"

I am running a new import from a SharePoint list each time so the user of the access DB can have the most current data. Is there a way to just to an append... its not a tremendous amount of data.

My bigger concern is why altering the data type with VBA is causing this error but if I manually go into the Design view of the table and simply switch from Memo to Text it doesn't have issue.

To proceed for now I am just going create a new table with all the necessary fields as TEXT and then do an INSERT from the SharePoint import.  Not ideal but I think best workaround for now.

Example:

Application.CurrentDb.Execute "CREATE TABLE RAMP_New " & "(Title TEXT(255), [Activity Nature] MEMO, Company TEXT(50));"
Application.CurrentDb.Execute "INSERT INTO RAMP_New" & "(Title, [Activity Nature], Company) SELECT Title, RAMP2015ActivityNature, RAMP2015Company FROM RAMP;"

Select allOpen in new window