Error using append query

Question

I have a number of linked ODBC database tables that I can open with no problems. I can use a query to open and filter the data as well, but when I try to use an append or make query to place the data into another table, I am getting the following error message.

            Cannot open database. It may not be a database that your application recognizes, or the file may be corrupt.

Any ideas as to why this would happen?

Answer 1

You can export a query (I just tested) except apparently not a pass-through. If you are using ODBC Server it is typically more efficient to use pass-through queries if all of your tables are on the same "ODBC Server". You can create an Access query with a source of a pass-through query.

Without knowing your situation, I would still recommend using fewer queries and making them SELECT queries. It looks like you are breaking apart a table, creating separate tables, and the appending them back together.

When I need to use temporary tables, I typically create a temporary ACCDB file to store them. This avoids the bloat associated with make table and append queries.

Answer 2

ok, I will take a look at the pass-through and see how that works.

Thank you for your help

Answer 3

I have never used a select query to export any data. Is that going to be faster, or better than then TransferText from a table?

I do need the first set of make tables, so that I have individual tables for that data. As for the third group in the code, that starts with make_56, I am doing this because the data comes from the ODBC server and I felt having one table with all the data would be faster than using a union query. Every time we would run a query, it would have to pull in the data from all 6 linked tables before running the final query. With the table, all the data is already there, so I assumed it would be quicker to run. Am I incorrect in making that assumption?

The last group in the code, yes, I can run a union query on that one, because it is pulling from the tables made earlier in the code.

Answer 4

You are possibly correct that the size of your file could be causing issues. 

It looks like you are running a bunch of make table queries and then exporting them. Have you considered just exporting from a select query? Is there a good reason for all of the make table and append queries when possibly select and union queries might suffice?

Answer 5

ok, I did a little more digging into this. First, there is a username and password for accessing the ODBC servers, which I did enter at the start of the code. For whatever reason, the system seemed to forget that I entered this in, while I was developing more queries to put into the code.

Now, the code runs, but when it gets to the last line, just before the msgbox, it gives me that same error message. What I did notice is, my database goes from 5.8M to 1.99G during the course of running the code. Could this be causing my problem and not the actual access to the ODBC server? 

Here is the code that I am running.

Private Sub Command0_Click()

    DoCmd.SetWarnings False

    DoCmd.OpenQuery "make_tblAMAPS_BOM_16_1", acViewNormal, acEdit

    DoCmd.OpenQuery "make_tblAMAPS_BOM_17_1", acViewNormal, acEdit

    DoCmd.OpenQuery "make_tblAMAPS_BOM_46_1", acViewNormal, acEdit

    DoCmd.OpenQuery "make_tblAMAPS_BOM_56_1", acViewNormal, acEdit

    DoCmd.OpenQuery "make_tblAMAPS_BOM_57_1", acViewNormal, acEdit

    DoCmd.OpenQuery "make_tblAMAPS_BOM_85_1", acViewNormal, acEdit

    DoCmd.TransferText acExportDelim, "AMAPS_BOM", "tblAMAPS_BOM_16_1", "C:\Users\Desktop\AMAPS_BOM_16_1.TXT", True, ""

    DoCmd.TransferText acExportDelim, "AMAPS_BOM", "tblAMAPS_BOM_17_1", "C:\Users\Desktop\AMAPS_BOM_17_1.TXT", True, ""

    DoCmd.TransferText acExportDelim, "AMAPS_BOM", "tblAMAPS_BOM_46_1", "C:\Users\Desktop\AMAPS_BOM_46_1.TXT", True, ""

    DoCmd.TransferText acExportDelim, "AMAPS_BOM", "tblAMAPS_BOM_56_1", "C:\Users\Desktop\AMAPS_BOM_56_1.TXT", True, ""

    DoCmd.TransferText acExportDelim, "AMAPS_BOM", "tblAMAPS_BOM_57_1", "C:\Users\Desktop\AMAPS_BOM_57_1.TXT", True, ""

    DoCmd.TransferText acExportDelim, "AMAPS_BOM", "tblAMAPS_BOM_85_1", "C:\Users\Desktop\AMAPS_BOM_85_1.TXT", True, ""

    DoCmd.OpenQuery "make_56_All_Item_Master", acViewNormal, acEdit

    DoCmd.OpenQuery "app_16_All_Item_Master", acViewNormal, acEdit

    DoCmd.OpenQuery "app_17_All_Item_Master", acViewNormal, acEdit

    DoCmd.OpenQuery "app_46_All_Item_Master", acViewNormal, acEdit

    DoCmd.OpenQuery "app_57_All_Item_Master", acViewNormal, acEdit

    DoCmd.OpenQuery "app_85_All_Item_Master", acViewNormal, acEdit

    DoCmd.OpenQuery "make_16_All_BOMs", acViewNormal, acEdit

    DoCmd.OpenQuery "app_17_All_BOMs", acViewNormal, acEdit

    DoCmd.OpenQuery "app_46_All_BOMs", acViewNormal, acEdit

    DoCmd.OpenQuery "app_56_All_BOMs", acViewNormal, acEdit

    DoCmd.OpenQuery "app_57_All_BOMs", acViewNormal, acEdit

    DoCmd.OpenQuery "app_85_All_BOMs", acViewNormal, acEdit

    MsgBox "Exports Complete"

End Sub