Whenever I set up an export wizard to export a query to an access database, the file name I enter is overridden with “ToadQueryFile2011-03-28.accdb”, is there a way to prevent this?
The file name you describe is a suggested default name. Click on the (…) browse to file button and select directory name and file name. Make sure and select the Overwrite option is that is what you want.
I think there is a bug where the file name will not take if you manually enter the name and not use the browse button. This is on a list to be fixed. But in the meantime the above will work.
Debbie
Debbie
I used the "..." button and the file is using the correct name, however, the date keeps getting added to it. I also do not see the "overwrite" option (see attached)
Debbie
I used the "..." button and the file is using the correct name, however, the date keeps getting added to it. I also do not see the "overwrite" option (see attached)
Debbie,
I also forgot to mention, whenever, I open the new database it always “compact & repair database”.
Does the access database already exist? Try pointing to an existing database and an exsiting table. Choose truncate if you to not want to append.
The compact & repair database message seems odd. What version of Access are you using?
Debbie
I will give that a try today and let you know what happens. I hadn’t tried that because I thought a fix was required (see below) to append to an existing table.
http://tda.inside.quest.com/message.jspa?messageID=112997#112997
Forgot to mention I am using Access 2010 and TDA 64bit
Good point. CR80989 is scheduled to be fixed in TDA 3.0. Let me know of the status of the above steps.
Seems to work now with an existing database, I did notice however if you change the field types it will crash. The majority of my text fields exported as memo so I had to create a secondary process to convert.
If you are using an existing Access database and table you can try doing it a different way. This uses Import and is only avilable in TDA 2.7. Try these steps.
- Connect to both the database you want to export from and the Access database to import to.
- Make the access database the current connection.
- Open up the Import Wizard and choose "Add Query".
- Add a query that selects from the table you used in the original export. Change the connection in the preview window to the correct database and preview the data.
- Export to existing table in Access or create new table. If you create new table you can change the column types where you could not in export.
- Run the import.
See if that is a better way for you.
Debbie
I saw that method in another post and tried it, however, I got some kind of Microsoft ODBC error. I can recreate the error message if you want, perhaps I just need to change a setting?
I will need to see the error message and also the import log. At the end of the import you will see a result window. It has a hyperlink to an error log. Press that and copy and paste the entire log and post.
Debbie
3:59:36 PM Thread (20) Import Started [3/29/2011 3:59:36 PM]
3:59:36 PM Thread (20) Processing “Query” into a new table C:\Sample.accdb.TestNew
3:59:36 PM Thread (20) Droping Table
3:59:36 PM Thread (20) Creating table TestNew
3:59:36 PM Thread (20) CREATE TABLE TestNew (POOL VARCHAR(50),
COLLCODE VARCHAR(50),
VEHICLE VARCHAR(50),
NM_LEGAL VARCHAR(87),
NM_LMS VARCHAR(60),
NM_INTERNAL VARCHAR(87),
NM_EXTERNAL VARCHAR(83),
Type VARCHAR(50),
COMMINGLED VARCHAR(50),
LENDING VARCHAR(50),
DATADATE DATETIME)
3:59:42 PM Thread (20) Heterogenous Import 3/29/2011 3:59:42 PM
3:59:42 PM Thread (20) Importing 1 out of 1 files
3:59:42 PM Thread (20) Reading from file PoolXref.tsm
3:59:42 PM Thread (20) Row 1 failed to insert, error: [Microsoft][ODBC Microsoft Access Driver]Invalid character value for cast specification
3:59:58 PM Thread (20) Inserting data into table TestNew generated an error. [Microsoft][ODBC Microsoft Access Driver]Invalid character value for cast specification
3:59:58 PM Thread (20)
Import canceled, some rows may have been inserted. Change AutoComitt to Off to be able to rollback errors.
3:59:58 PM Thread (20) Error importing data, please check file format options: Inserting data into table TestNew generated an error. [Microsoft][ODBC Microsoft Access Driver]Invalid character value for cast specification
I did a test similiar to yours and got the same error. We are not handling the date column correctly. I have entered CR83,966 to fix.
To work around this, cast your date column to a string and change your column data type in the table. If you are using Oracle add a to_char(date_column) and then in the import table window change DATETIME to VARCHAR(NN).
Debbie