Can't change data types in design view

I need to change the datatype from text to OLE object to store
an image. All files in the db are closed except for the table that
needs the change. I've noticed that it won't let me change ANY
datatypes on any fields to anything other than what they are

What to do?

Post your answer or comment

comments powered by Disqus
I have a simple table with about 20 or so fields and about 390K records. This same structure is in three local databases (one for each business unit). One of the fields was incorrectly imported as text instead of date. I would go back and re-import the data but that is a complete pain. It comes from about 10 similar txt files per database and I would rather fix the data already in the system than re-import 30 txt files.

I tried to change the data type in DESIGN VIEW but that says I don't have enough disk space or memory to complete the change. Not sure why since I have over 100G free HDD space and 2G RAM Free (4G total).

I then did a quick query to pull out the data from specific years to see if that would help. I pulled out one year, went into DESIGN and same error. Even though I only have 51K records this time.

I then tried ALTER TABLE on the pulled data with:


It looked like it would work when it warned me I didn't have enough memory for an undo. It ran a bit further then came back with a "File sharing lock count exceeded" error and told me to increase the "MaxLocksPerFile" setting in the registry. I increased it from the default 9500 to 128000 and restarted Access. No good. Same error.

HELP! What am I doing wrong? Any ideas on how to get this data type changed without re-importing all the data?

I have inherited some Foxpro tables which I am trying to convert to Access and I hae set the exported tables up and have tried to set the relationships between the tables so that I can run queries and setup forms to review and add new records. However I am struggling to get the relationships in place that will allow me to do this. When I try to drag the common fields from one table to another to setup the link I get a screen which shows Relationship Type as 'indterminate' rather than 'one-to-many'. Whatever I do I can't change the type of link and when I view a query based on the two linked tables the * in the record navigator is greyed out so I can't add new records.
I have a sample of the tables,links,queries and forms I have setup to date (with a limited number of records), if someone could have a look at it and advise where I am going wrong that would be great.
The data and the structure of the tables is pretty much fixed as this is what is coming through from Foxpro and there is a lot of historical data to bring across.
Many thanks

Hello all,

Am very new to Crystal - and have just learned that it won't sort or group on a Memo field (Text >256 chars).

SO - it has been suggested that I use a VIEW on the SQL server to pull the required data out, and at the same time, changing the data type in the view so that it is just text, not memo (the data stored in the field is only ever 10-15 characters long, so data clipping won't be an issue.).

I, however, cannot see how to change the data type for the life of me - it is very important the data type ISN'T changed in the main table, only in this view.

Any ideas?

Thank you in advance!

Need your help!

I have a large spread sheet with about 700000 rows, which is imported from a text file. I need to change data type in some columns from text to number but Access doesn't allow me to do so. It says "not enough memory/space/storage to complete...". I have tried to change the MaxlocksPerFile, and also run a update query to update the data, but it doesn't work. Can you guys help me on that?

Thanks a lot!

Hi! I have a problem to build Pivot table in Excel 2003.
I’m creating this table base on the “External Data” which is a MS Access 2003 table.
The table has 50 fields and about 500.000 records. (This is the reason, I can’t just export table to Excel and then do pivoting). One of the fields of this table has a “text” type but stored numbers. Excel does not allowed me do Sum or Max function with this field – it needs to have Number data type.
I receiving this table “from outside”, so I can’t get the right data type from the beginning. If I’m trying simply opening the table, before, using as a data source for Pivot Table, in Design mode and just change data type from text to number, I have an error: "Microsoft Access can't change the data type. There isn't enough disk space or memory."
Any advice, how to change Data type in existing table using queries or something else what can help me to solve this problem.

Thank you very much at advance.


Hi all,

I'm trying to group records by year. Tried typing in the following in Design View:

Year: DatePart("yyyy",[SurveyDate]) into the Field row in Design view. It yields #Error all the way down.

Then I realized my SurveyDate field was a "Text" field, not Date/Time. Figured that's probably the problem. So tried to convert it to Date/Time and got the following error:

---"Microsoft Access can't change the data type. There isn't enough disk space or memory."

Did a search on this forum and found that this is a common problem with big databases (mine's 534,000 records). So I tried to create a new table with all the same fields -- but with SurveyDate as a Date/Time type, and append all the data from the old table into it.

I'm getting the following error:
---"Microsoft Office can't append all the records in the append query.
Microsoft Office Access set 534582 fields to Null due to a type conversion failure"

Any ideas? I wasn't sure how to do the other option suggested in that thread: creating a make table query and using the str() and lng() function to change the data type. [Here's a link to that thread:]

Help, o brilliant Access wizards!

Ok, been puzzled by this one.
I have a form with a subform in it. The subform is based on a query. It shows all my active staff. On the form i have a button that (atleast i would like that it does that) when clicked it sets the field "selected" on true on the subform.

The code behind the button works just fine, but i always get an error stating, can't change data, database or object is read only. Funny thing is, when i click on the checkbox "selected" in the subform behind each staff member, i can set it to true. Why does it not work? I tried it with selecting the query itself (qerWerknemersActief instead of tblWerknemers) and also not working. Here's the code, hope someone has a clue. Oh, in each form everything is set on yes except adding records, so it's not that change records is set to no.

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As Variant

strSQL = "SELECT tblWerknemers.Id, tblWerknemers.Werknemer, tblWerknemers.Selectie, tblWerknemers.Actief " & _
"FROM tblWerknemers " & _
"WHERE (((tblWerknemers.Actief)= True));"

Set db = CurrentDb

Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly)
Do Until rs.EOF
rs!Selectie = True

Set rs = Nothing
Set db = Nothing
Call Form_frmBeheerDeelnemers.Requery

Access 2003 - Is there any way to change the data type in a "Make Table Query"? I have tried doing a format on the date columns and this doesn't work. I know there are coding ways to do it, but I thought this would be a simple feature in a UI like Access.

Thank you,

Hi there

I'm using Access 2007 to import data from Excel using VBA. I have this code:

Private Sub cmd1Importa_Click()
Dim myRec As DAO.Recordset
Dim strQry As String
Dim dbExcel As DAO.Database
Dim rsExcel As DAO.Recordset

Set myRec = CurrentDb.OpenRecordset("Tabela1")

Set dbExcel = OpenDatabase("C:BasesTestesTestesTabelaParaImpo rtar.xlsx", False, True, "Excel 12.0; IMEX=1;")
Set rsExcel = dbExcel.OpenRecordset("Plan1$")

Do While Not rsExcel.EOF
myRec.Fields("Coisa1") = rsExcel.Fields("Coisa1")
myRec.Fields("Coisa2") = rsExcel.Fields("Coisa2")
myRec.Fields("Coisa3") = rsExcel.Fields("Coisa3")

End Sub

But the ODBC driver reads only the first 16 records and chose for the column 3 (Coisa3) data type String with 255 characters max. And in the worksheet that I have texts with over 255 characters that are not being imported. I can choose the data type of the third column as memo? How?



On my main form, I would like to disable the right click so user can't change the form to design view. I checked the properties and could only see allow form view, and that is set to yes. Can anyone help?


I have an ODBC link set up between a DB2 table and MS Access. A date column from the DB2 table is coming through as a text column in Access. I can't change the datatype in design view in Access. Is there a way to convert the text into a date format in a query?

This is an example of the text formatted date. I want to convert to something as simple as "2001-12-14". want to be able to run some easier queries for date ranges but this text format is giving me trouble.



I'm a bit of a newbie, so lets just get that out of the way....

I have a field in a table that was originally a text data type. I want to change it to a "lookup" data type using the lookup wizard. However, Access doesn't allow me to do this and prompts me with "You can't change the data type....its part of one or more relationships...". But there are no relationships? There may have been previously, but I've deleted them all from Relationships window.

This has happened several times now. I am in the process of designing the database and have been changing my mind about data types when I realize that I can use a lookup data type. The only fix I have found is to make a copy of the table in question and then change the data type of the problem field. Unfortunately this screws up my forms...which is a pain.

Why would Access say that there are relationships in the relationships window when in fact there are none?

I've attach a copy of my db. The field in question is located in the "Activities" table. The field name is "ACTIVITY_NAME". I am trying to use a lookup from the activityNames table using the "ActivityName" column.

Thanks for any help in this matter.

Hello all,

I have an issue with my DB which is split into FE/BE: on the stage of initial design, when I created the data tables, for some of the fields I chose the text type which, as I later discovered, only allows the record to be

Visual Basic run-time error 7792: You can't open a subform when it is also open in Design view.

I have a subform with properties for SourceObject, LinkMasterFields, LinkChildFields changing according to objects and events in the master form.

The subform was bound to a query before which meant that the subform load event was happening as soon as the master form is loaded. I needed to restrict the subform loading until a certain point so I removed the SourceObject property for the masterform's subform. The subform on the master form is now Unbound.

(This is because I'm now running some code on the FormLoad event for the subform which needs to be restricted until the LinkMasterFields and LinkChild Fields properties have been assigned correctly otherwise it takes ages to load.)

Now I'm getting the above error. Obviously, i do not have the subform open anywhere in design view. No Visual Basic windows are open. I've closed the db, closed access, reopened it and clicked on nothing except the masterform. The error occurs when I raise the event in the master form which assigns the sourceobject property to the subform, i.e.

Me.sfmQryAllOV.SourceObject = "sfmQryAllOV"

Can anyone help me? I've googled this but finding no answers.

Hi All

I know there has been a lot of posts about this subject but I can't seem to find a solution.

I have a split DB. FE on each client and BE on network drive. When running the Db in working mode all is well and speed is not an issue. However when I'm updating or adding queries,reports or forms (local development version) it runs like a donkey in design view. It only affects those objects linked to the BE. I'm adding new tables and forms which have not yet been split and these run very fast in design view so it is not an issue with the PC.

I guess when changing objects linked to the BE it is pulling data over the network so slowing things up but what is the best practice for avoiding this.

Many thanks for any help given.

my teacher asked me to do Access homework to change data type from Text to Boolean. Data in "Owned car" field show as Y or N
so I click on Design View and change datatype of this field from "Text" to "Yes/No" , than when I saved it show a message
When I back to Datasheet view, all data in "Owned car" field change to be "No".

How can I change data type to be Boolean type without deleting my original data??? I want them to show the same as Y or N.

First of all Hello.

This problem has really got me stumpped. I have searched ton's of forums and haven't found anything that has helped.

I am updating a working database with some more user friendly form queries. I have set the query up to the main data table, created the query, and the form, then linked the various query fields to the appropriate fields on the form (all unbound form fields).

Now up to this point there are some minor issues but nothing that I can't fix on my own. When you access the form input various search parameters in the form fields and run the query it works just fine.

Now when I try to go back into the query in Design view it crashes access! I don't even get an error message access simple locks.

Solutions I've tried.I assumed at first there was an issue with my query fields linked to the form. So I rebuilt the query from scratch went over each and every field link command and got the same result multiple times. I ran the analyze and optimize tool I have changed the Yes/No fields and adjusted them to True/False thinking I was having an issue with Null fields in the query Nothing has worked. The form and query still function exactly as designed I just cant open the Design view on the query???? It has to be an issue with the query as the form opens in Design view.

Any help will be appreciated.

I may be missing something very obvious, but here goes. Thanks very much for reading:
i have a form with many check boxes and text boxes that follow them.
Example is: Eye Color? 1=brown, 2=blue, 3='other', explain 'other'... [text box]
I'd like to keep them all together in one box and in an effort to keep the number of controls down, I've stretched the Frame of the check box over the text box.
Now once I click an answer in the check box, I can't change the answer. Nothing happens. If I go out of the box and click elsewhere then return to the check box, I can change the answer ... once. Then it's the same thing again. If I go to Design View and "Bring to Front" the check box, it works correctly, but I lose the text box. There has to be a way to see both doesn't there? Is it only with a new empty box control?

As the title says, I'm trying to get a subform to display in datasheet view in design mode. I've tried:

Setting the subform's Default View to Datasheet in design view whilst viewing the subform directly (and, to be sure, viewing it from within the main form). Setting Allow Form View, Allow PivotTable View and Allow PivotChart View of both the main form and sub form to No.
I'm trying to do this in order to change the row height of the records in datasheet view. The records were imported from an Excel spreadsheet and contain carriage returns instead carriage returns and line feeds, which Access apparently requires. I want to ensure that I can have multi-line rows/cells in datasheet view before converting all the CRs to CR LFs.

Edit: It appears that multi-line rows in datasheet view is not possible. I'm still interested to know why I can't see the form in datasheet view in design mode, though.


(Using Access 2003 with file saved as Access 2000)


I have a form to display the hours worked by employees for a given four week period and hours carried forward under a flex time system.

Under the system I have created, users can save the hours worked and balance carried forward to a table, at the end of a period. But, after this point, changes can be made to the shifts and so the saved hours may be wrong. So, I am trying to create a system that allows the user to view stored hours and actual hours and if there are differences, then update the stored hours.

I am getting the following error when I try to access a recordset based on a query in VBA in order to make the changes:

Run-time error ‘3464’:

Data type mismatch in criteria expression.

I have searched through the VBA forum and queries forum and tried several of the possibilities from articles there, but still am getting the error message.

I am fairly sure the data types of the parameters I am passing to the query are the same as that required by the query (and the queries and tables it uses) but obviously am missing something.

(I wonder if it is something to do with the fact that the query I reference in the VBA only uses one parameter but two of the queries it draws on use two parameters)

Can anyone point me in the right direction or offer a link to something that might help?



More info:

I have a form: frm_Hours_Worked with two sub forms:

There is a combo box and text box on the form which are both formatted as Short Date.

The subform, Sfrm_Hours_Worked_Update, displays a recordset from a query that draws from a table and second query. The second query pulls data in from three other queries each of which refines data slightly.

When I click debug on the error message it highlights: Set rs = qdf.OpenRecordset()

The code follows:

	    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    Set db = CurrentDb()
    DoCmd.OpenForm "frm_Hours_Worked", , , , , acDialog
    Set qdf = db.QueryDefs("qry_Hours_Worked_Update")
    qdf("Forms!frm_Hours_Worked!cbxPeriod") = Forms!frm_Hours_Worked!cbxPeriod '.Value
    qdf("Forms!frm_Hours_Worked!txtPeriodEnd") = Forms!frm_Hours_Worked!txtPeriodEnd '.Value
    Rem !!!!!!!!!!!!!!!!!!!!!!!
    Rem following line results in error message:
    Rem Data Type Mismatch in Criteria expression
    Rem as far as I can tell, the data types in both the queries and forms are short dates
    Set rs = qdf.OpenRecordset()
    Do While rs.EOF = False
        Rem if there are differences between values of saved hours and actual hours or
        Rem balance carried forward hours then
        Rem change the saved hours to actual hours and balance hours as well
        If rs!HoursWorked  rs!HoursWorkedSum Or rs!BalanceCF  rs!BalCF Then
            rs!HoursWorked = rs!HoursWorkedSum
            rs!BalanceCF = rs!BalCF
        End If

I am trying to change a field that currently has a data type of number and I want to change it to a date/time data type. When I try to do this, I get a message that says there isn't enough disk space or memory. I am working with a database that has about 100,000 records in it.

I have a query with a simple parameter, [Enter the start date]. This is called by another query to add more data, and then by a report.
My problem is this. I go into the report in design view, and my parameter keeps popping up! It will not go away no matter how many times I try to cancel it - it just comes back. So I cannot make changes to the report. How can I stop this from happening?

Is there anyway to change the criteria in a field without going into design view I bascially need to be able to type in a particular item in a certain field in a query and it return all the results for that one.

The database has over 160000 records and when I try using a form it takes forever.

I have a text field that contains numbers that I want to change to a numeric field during a make table query. (Please note that I do not not have the option of changing the original table's data type.) (Also, I'm using Access 2000.)

I've been searching forum posts and see people changing data types during queries with operators like CInt, CDbl, CLng. I've tried all of these and still get the error "data type mismatch in criteria expression." What am I doing wrong? I've also tried using Val, and that works, but it gives me extra records I don't want.

Here is my SQL code using CInt:
SELECT CInt([CONINFO]![Contract/PO #]) AS PONum, CONINFO.[Title/Description], CONINFO.[Spec Writer], CONINFO.[Award Date] INTO tblPOCAO
WHERE (((CInt([CONINFO]![Contract/PO #])) Between [Enter low number range] And [Enter high number range]))
ORDER BY CInt([CONINFO]![Contract/PO #]);

Here is my SQL code without trying to change the data type:
SELECT CONINFO.[Contract/PO #] AS PONum, CONINFO.[Title/Description], CONINFO.[Spec Writer], CONINFO.[Award Date] INTO tblPOCAO
WHERE (((CONINFO.[Contract/PO #]) Between [Enter low number range] And [Enter high number range]))


Not finding an answer? Try a Google search.