Vba

[brianh]

The following VBA code works but the copy object part does not.:

**************************************************************************************************** ****
Private Sub Form_Load()
Dim strSQL As String

'sql string
strSQL = "Delete * from tblOutput"
DoCmd.SetWarnings False

'delete tblArchive
DoCmd.DeleteObject acTable, "tblOutputArchive"

'copy tblOutput to a new tblEMPArchive table
DoCmd.CopyObject "dbPhonelist", "tblOutputArchive", acTable, "tblOutput"

'make sure no warning messages come up

'run the sql query to delete everything in tblOutput, this way when
'we import it only adds the records being imported
DoCmd.RunSQL strSQL

'transfer text file into the tblOutput table
DoCmd.TransferText acImportFixed, "Standard", "tblOutput", "g:\phonelist\output.txt", True

DoCmd.Quit acQuitSaveAll

End Sub
**************************************************************************************************** ****

At the end after it closes the DB it says "Cannot find c:\windows\desktop\dbphonelist.mdb or one of its components."

And the copy does not get accomplished..Any ideas?

[Mega_Man]

It looks to me as if you are trying to enter records in a table called tblOutputarchive but in the previous line of code you are deleting this table instead of just deleting the records within the table.

Try recreating the table before running the copy command.

Mega.

[brianh]

Well I ran a SQL query to delete all records of the current tblOutputArchive and then copy it. Here is the code:

Private Sub Form_Load()
Dim strSQL As String
Dim strSQL2 As String

'sql string
strSQL = "Delete * from tblOutput"
strSQL2 = "Delete * from tblOutputArchive"

'make sure no warning messages come up
DoCmd.SetWarnings False

'copy tblOutput to a new tblEMPArchive table
DoCmd.RunSQL strSQL2

'copy table to tblOutputArchive
DoCmd.CopyObject "dbPhonelist", "tblOutputArchive", acTable, "tblOutput"

'run the sql query to delete everything in tblOutput, this way when
'we import it only adds the records being imported
DoCmd.RunSQL strSQL

'transfer text file into the tblOutput table
DoCmd.TransferText acImportFixed, "Standard", "tblOutput", "g:\phonelist\output.txt", True

DoCmd.Quit acQuitSaveAll

End Sub

It does not copy the contents of the table. The tblOutputArchive is there with nothing in it. It still gives that darn error message.

"Cannot find "c:\windows\desktop\dbPhonelist.mdb".....

I have no clue why that error comes up...Any thoughts..Thank you for your assistance.

[Mega_Man]

It is very difficult to debug without having the program in front of me. Either attach the MDB file so I can have a look or.
Recreate what you want within access in the form of a macro, then convert the macro to VBA and compare its code with what you have written.

Mega.

[brianh]

Here is the db, remember to hold down shift because it will run automatically.... This is working fine now except when it closes it gives you the error message:

Cannot fond the file 'C:\windows\desktop\dbPhone.mdb' (or one of its components). Make sure the path and filename are correct and that all required libraries are available.

[Mega_Man]

I am at work just now, so I will have a look at this when I get home tonight.

Mega.

[brianh]

Thanks Mega, does anyone else have an idea about what this could be?

[Mega_Man]

Brian,
Sorry mate, I have just downloaded your file and it looks as though you have created it in Acess 2000. I only have Access 97 at home.
This will have to wait until I get back to work tomorrow.
I promise I will look at it first thing in the morning and get back to you soon thereafter.

Mega.

[brianh]

This one is converted to 97....

[Mega_Man]

This is not your night. When I run this I get "Couldn't find installable ISAM".

Having no idea what that is, I will have to find out.

Mega.

[brianh]

Mega (or anyone else),

I think that it is a library reference that I don't have.. I have no clue which one it would be though. Do you have any idea?

[Mega_Man]

Ok Brian, I have had a look.
I was somewhat confused as to why you are:

1. Deleting all records in one table.
2. Copying the contents of another table back into the first table
3. Then trying to populate the table from a text file.

Please clarify exactly what you are trying to do.

I have set up a reference to the Scripting Runtime object to check if the file exists or not.
I assumed that you were trying to export the contents of the table back into a text file, in which case using AcImport would not have done that. (if that's what you want). I have therefore changed this to AcExportDelim.

Anyway, have a look. If it is not what you want, please specify exactly what your requirements are and I will have another look.

Mega.

P.S. Give me your e-mail address as the file is now too large to send via the forum.

[brianh]

[email protected]

What I was trying to do is delete one table (tblEmparchive) then copy the current tblEmp table to tblEmparchive. Then delete everything in tblEmp and import from a text file into tblEmp. Then save and close Access.

Thanks

[Mega_Man]

I am not going to have time to look at this again until tomorrow but could you send the text file you want imported with some test data in it.

The only major change I made was to implement the filesystemobject to ensure that the file existed.. Before I did this, I go the same error message that you are getting.

Mega.