INSERT INTO statement was not found in field - I'm so lost

[Wookiefoot]

Bare with me. I'm fairly new to access and I'm taking over a database that was built by someone else with zero previous experience.

I'm getting "The INSERT INTO statement contains the following unknown field name: 'Draka Part #' Make sure you have typed the name correctly, etc"


So I go into the TRExportMasterTrunks macro, check the Q_MakeMasterTrunks query to make sure everything is spelled correct and it is.

It auto populates 'Draka Part #' after entering the Table Name which I'm pulling the column headers from 'FIBER TRUNKS' (linked excel spreadsheet).

In the 'MasterTrunks' table, that the Q_MakeMasterTrunks

SELECT [FIBER TRUNKS].[(EEFGH)], [FIBER TRUNKS].Color, [FIBER TRUNKS].[Fiber Count], [FIBER TRUNKS].[Fiber Type], [FIBER TRUNKS].[Cable Color], [FIBER TRUNKS].Description, [FIBER TRUNKS].[Corning Part #], "" AS Corning_MatNum, 1.11 AS Corning_Inv, "" AS Corning_units, 1.11 AS Corning_StdPrice, [FIBER TRUNKS].[Superior Part #], "" AS Superior_MatNum, 1.11 AS Superior_Inv, "" AS Superior_units, 1.11 AS Superior_StdPrice, [FIBER TRUNKS].[AFL Part #], "" AS AFL_MatNum, 1.11 AS AFL_Inv, "" AS AFL_units, 1.11 AS AFL_StdPrice, [FIBER TRUNKS].[TLC Part #], "" AS TLC_MatNum, 1.11 AS TLC_Inv, "" AS TLC_units, 1.11 AS TLC_StdPrice, [FIBER TRUNKS].[OCC Part #], "" AS OCC_MatNum, 1.11 AS OCC_Inv, "" AS OCC_units, 1.11 AS OCC_StdPrice, [FIBER TRUNKS].[OFS Part #], "" AS OFS_MatNum, 1.11 AS OFS_Inv, "" AS OFS_units, 1.11 AS OFS_StdPrice, [FIBER TRUNKS].[Draka Part #], "" AS DrakaPrysmian_MatNum, 1.11 AS DrakaPrysmian_Inv, "" AS DrakaPrysmian_units, 1.11 AS DrakaPrysmian_StdPrice, [FIBER TRUNKS].[Hitachi Part #], "" AS Hitachi_MatNum, 1.11 AS Hitachi_Inv, "" AS Hitachi_units, 1.11 AS Hitachi_StdPrice, [FIBER TRUNKS].[Berktek Part #], "" AS Berktek_MatNum, 1.11 AS Berktek_Inv, "" AS Berktek_units, 1.11 AS Berktek_StdPrice, [FIBER TRUNKS].[Commscope Part #], "" AS Commscope_MatNum, 1.11 AS Commscope_Inv, "" AS Commscope_units, 1.11 AS Commscope_StdPrice INTO MasterTrunksFROM [FIBER TRUNKS];

query creates, I notice that the data type for Draka Part # is Short Text instead of Yes/No.

When I attempt to change the data type to match the others, it says there is not enough memory or space to change data types.

Would this possibly be causing the error I'm getting? If so, I've read a few workarounds by creating copy of the table and making an append query to change the data types, but I'm still a bit lost on that part.

[jwhite]

I feel your pain having to work with this database with VERY BAD table field names. Try wrapping the variable with [] everywhere in the query - [Draka Part #]. This would need to be done with any field name that contains spaces or non-standard characters in it. I would do it for the fieldnames with just a number as well (that is so weird....).

[Wookiefoot]

In the code I posted above it seems I have [] around each variable already, no? I'm a novice so I may be looking in the wrong place.
I believe it allowed me to change the data type from Short Text to Yes/No once and it got rid of the error but for some reason it didn't save. Now when I try it gives me

Hmm, off to google I go.

[davegri]

Do a database compact/repair, then try changing the data type again.

[jwhite]

Apologies, I didn't look closely at your images. Why try to change from Short Text to Yes/No? If you just got this database handed to you, doing that probably isn't a safe thing to do. Without seeing the database, don't know. Perhaps try to Compile/Compact/Repair the database and see if that makes a difference. With that error message about disk space, look at https://social.msdn.microsoft.com/Fo...orum=accessdev

[Wookiefoot]

Do a database compact/repair, then try changing the data type again.

I tried that a few times already. I even put the database on our network with plenty of space/memory and it gave the same space error as running locally. It's roughly 140mb compacted so I'm sure why 380gb of freespace and 32gb ram on a server isn't enough.

[Wookiefoot]

Apologies, I didn't look closely at your images. Why try to change from Short Text to Yes/No? If you just got this database handed to you, doing that probably isn't a safe thing to do. Without seeing the database, don't know. Perhaps try to Compile/Compact/Repair the database and see if that makes a difference. With that error message about disk space, look at https://social.msdn.microsoft.com/Fo...orum=accessdev

No worries, I'll check that link out. The Draka Part # is something I was requested to add. I followed the same format as the other vendors we have added. I'll try to work around the space issue and then my best bet seems to get a hold of the original developer.

[davegri]

If you have a split database, you can't change the table structure of a linked table from the front-end. You need to load the back-end to do it.