Background

In this wiki article, we will explore the solution to fix the missing tables like EQUIP, INSTR when a SQL based packaged project is unpackaged into Access mode using Project Packager tool. Such issues can be seen when the SQL database column contains incorrect length value for the defined datatype.

Steps to Accomplish

1. Using SQLMS (SQL Server Management Studio), create a blank database.
2. From Project Packager, unpackage the packaged project into SQL based project.
3. From SQLMS, check if all the tables are available to SQL database.
4. Navigate to the concerned table, in this case dbo.EQUIP table. Right click and select Design.
   
   
   
   
5. From Table Design, scroll to the list of columns and inspect the datatype and its length for each column. Notice for any odd length values.
6. In this example, LOOP_TITLE column has datatype as varchar, but the defined length value is 256.
   
   
   
   
7. For varchar datatype, maximum length can be from 0 to 255 byte. But in this example, it is defined as incorrect length value. Due to this concern, whenever the project is unpackaged into access format, it skips the entire table.
8. To fix this concern, we need to correct the varchar length and it should be defined as 255. In this example, we have three tables where some columns have defined length as 256 for varchar datatype.
9. To modify the length, we need to define ALTER query and fix it. The general SQL query should be
   
   SQL Query: ALTER TABLE {table name} ALTER COLUMN {column name} VARCHAR(255)
   
   
10. In this example, dbo.EQUIP, dbo.INSTR and dbo.INSTYPE tables have incorrect column length for LOOP_TITLE, LOOP_TITLE and INSTRUMENT_NAME respectively.
11. From SQL query, define query as
    
    ALTER TABLE dbo.equip ALTER COLUMN LOOP_TITLE VARCHAR(255)
    ALTER TABLE dbo.instr ALTER COLUMN LOOP_TITLE VARCHAR(255)
    ALTER TABLE dbo.instype ALTER COLUMN INSTRUMENT_NAME VARCHAR(255)
    
    
    
    
12. Click on Execute to run the query.
13. Once query executed, refresh the SQL database. Cross-validate the column length and it should show as 255.
    
    
    
    
14. Using Project Packager, package the SQL project database into Access type.
15. Unpackage the packaged project and check for tables. It should show the tables for EQUIP, INSTR and INSTRUMENT_NAME.
    
    
    
    

See Also