Results 1 to 5 of 5

Random Field Change from Datatype Number to Short Text in One of Three Tables

  1. #1
    mbeech is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    Ohio, USA
    Posts
    3

    Random Field Change from Datatype Number to Short Text in One of Three Tables

    I'm sorry if my terminology is off, I honestly have almost no experience with doing this type of thing in Access.

    I inherited an Access database recently and am having some issues. This one in particular has me puzzled.



    I have three tables in Access linked to Excel workbooks on a network drive. The three are all supposed to have a site number field that is datatype number. For whatever reason, one table keeps changing the site number field to a datatype of short text. This gives a type mismatch error when joining it to other tables using that field.

    If I go to design view for that table in Access I get "Table 'foo' is a linked table whose design can't be modified. If you want to.. change their properties or data types you must do so in the source database," which I understand. I can see the field is now short text, but I can't change it obviously (I get "Operation is not supported for this type of object").

    So, I go to the source database, an Excel workbook. It's shows as an Excel 2007+ file, but I can only open it if I change it to an xls extension. When I look at the field in there it shows as general, so I don't know if that has anything to do with the problem or not.

    The queries used to update the database are all, essentially, identical. I got desperate and tried to use Val() around the part of the join statement using that specific database and field. Still no luck.

    The three Excel files that are used to update these database files all have the site field as text but in Access the tables of the first two show as number.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,571
    tables dont change datatypes on their own, BUT, if you are importing XL files using a 'MAKE table' or the 'IMPORT spreadsheet to new table' this can happen.

    You should not use make table, instead, have the table already created with the correct datatypes, then import (append) the data.
    This keeps the datatypes permenant.
    Instead of import spreadsheet to new table, import to a target existing table.
    or
    link the XL file as an external table, then run an append query.

  3. #3
    mbeech is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    Ohio, USA
    Posts
    3
    Hmm, I don't see MAKE Table or IMPORT... anywhere in the query or the macro for the form button.

    Query:
    Code:
    UPDATE ltbl_Deploy_ops_upload_OP 
    INNER JOIN tbl_milestones ON Val(ltbl_Deploy_ops_upload_OP.Site_num) = tbl_milestones.site_num 
    SET tbl_milestones.track_last_updated = Date(), tbl_milestones.track_open = [ltbl_Deploy_ops_upload_OP]![track_Open]WHERE (((tbl_milestones.site_project_type) In ("1-New","2.2-Remodel","2.3-Refresh","FDP","Image Activation","Refresh")));
    Macro:
    Code:
    ...
        DoCmd.SetWarnings False
        Call fShowMsg(Forms!Maintenance, True)
        DoCmd.OutputTo acOutputQuery, "qry_Gateway_Date Changes", "ExcelWorkbook(*.xlsx)", "C:\temp\qry_Gateway_Date_Changes.xlsx", , , , acExportQualityPrint
        DoCmd.OpenQuery "qryU_deploy_ops_upload_CS", acViewNormal, acEdit
        DoCmd.OpenQuery "qryU_deploy_ops_upload_TO", acViewNormal, acEdit
        DoCmd.OpenQuery "qryU_deploy_ops_upload_OP", acViewNormal, acEdit
        DoCmd.OpenQuery "qryU_deploy_ops_removedeinstall", acViewNormal, acEdit
        DoCmd.OpenQuery "qryU_deploy_ops_phantum", acViewNormal, acEdit
        Call fShowMsg(Forms!Maintenance, False)
        Application.FollowHyperlink "C:\temp\qry_Gateway_Date_Changes.xlsx"
    ...
    Edit: Updating using linked table manager does nothing.

    In fact, it's not even the same file consistently like I thought. It seems to be random, which I'm sure can't be.

    I'm sorry for the amateur questions, but I truly appreciate the help. This is a side project I'm trying to get done between downtime on the main project. I'd like to get it done before my contract is up in a week and a half since no one else here wants to touch it.

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,082
    Excel is one of the worst file types to import data from because the cells are a variant datatype. What happens when trying to import is the driver looks at the underlying data of the first few rows (can't remember if it is 8 or 16) to determine the datatype. If it sees a mixture of numbers and text, it will assume the datatype is text. If it sees blanks, it will assume it is text. if it sees numbers it will assume numbers. What you see (the format property) is not necessarily what the underlying data looks like. If it sees numbers and on row 17 or later there is some text, you will get an error.

    So you have to force the issue. You can import without headers so the first row will be text, or you sort it to get the required formats at the top

  5. #5
    mbeech is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    Ohio, USA
    Posts
    3
    EDIT: I think it's solved. As long as nothing is overwriting those tables we should be good! Thank you all!

    Thank you Ajax, I think that may have worked. It's hard to say since I don't know where the original files get their data... I know it's not making sense with how I'm wording it. Stay tuned, we'll see how it does in production, as such.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-23-2018, 02:12 PM
  2. Replies: 2
    Last Post: 07-13-2016, 08:28 AM
  3. Replies: 1
    Last Post: 05-18-2016, 09:46 AM
  4. Replies: 2
    Last Post: 02-10-2015, 02:12 PM
  5. Replies: 8
    Last Post: 05-11-2012, 04:41 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums