Access - VBA Alter a field in table - settings error

I have VBA set to alter a field from an imported table from SharePoint. For some reason when I use the following code to modify the date type from Memo to Text

ALTER TABLE RAMP2015 ALTER COLUMN RAMP2015Company TEXT(50);

Open in new window


It produces the following error when I try to run a query against the table;
The setting your entered isn't valid for this property.

If I modify the data type manually I do not encounter the error. How can I adjust my code to make sure it works as if I am doing it manually?
aehrenwoTechnology AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
how are you running the "Alter table... ?

currentdb.execute "ALTER TABLE RAMP2015 ALTER COLUMN RAMP2015Company TEXT(50);"
0
aehrenwoTechnology AnalystAuthor Commented:
Yes.

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

Open 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
0
Rey Obrero (Capricorn1)Commented:
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 ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rey Obrero (Capricorn1)Commented:
also, in the VBA window do DEBUG>Compile
make sure it compiles without error
0
aehrenwoTechnology AnalystAuthor Commented:
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

Open 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.
0
Rey Obrero (Capricorn1)Commented:
before you run the "alter table statement..

place this code

RefreshDatabaseWindow

' run your alter tables here
0
aehrenwoTechnology AnalystAuthor Commented:
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?
0
Rey Obrero (Capricorn1)Commented:
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"
0
aehrenwoTechnology AnalystAuthor Commented:
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;"

Open in new window

0
Rey Obrero (Capricorn1)Commented:
<My bigger concern is why altering the data type with VBA is causing this error >

try creating a new blank db.
create a table (table1) with a Memo type field (mymemo)

run this in the immediate window of the VB editor

currentdb.execute "alter table table1 alter column mymemo Text(50)"

see if that will work.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.