Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • Last Modified:

Importing Text files using Code problems

I have a database that has been converted from Access 97 to 2000.  There is a button on one of the forms that contains code which should import text files from a shared network drive (I:\) delete and replace the data from some of the tables.  I have made sure i have access to the required files on this drive, but when i run the code i get the error message:
"The text file specification 'cc_acct' does not exist. You cannot import, export, or link using the specification"
This worked when it was in Access 97 but for some reason doesn't work now, i definately have access to the files!
See code below.

Option Compare Database   'Use database order for string comparisons
Option Explicit
Dim S As String, N As Integer, n2 As Integer, M As String, CN As Integer, CR As Integer

Private Sub Button58_Click()
On Error GoTo Err_Button58_Click

Dim s1 As String, F As String
Dim T1 As Double, T2 As Double, T3 As Double, T4 As Double, T5 As Double, T6 As Double

    T1 = Now

    DoCmd.Hourglass True
    DoCmd.SetWarnings False

'   Empty Import Files

S = "DELETE DISTINCTROW CC_ACC.* FROM CC_ACC;": DoCmd.RunSQL S
S = "DELETE DISTINCTROW CC_CAMP.* FROM CC_CAMP;": DoCmd.RunSQL S
S = "DELETE DISTINCTROW CC_CONT.* FROM CC_CONT;": DoCmd.RunSQL S
S = "DELETE DISTINCTROW CC_SECT.* FROM CC_SECT;": DoCmd.RunSQL S

'   Import Data


    F = DLookup("Path", "tblFilenames", "[File]='ACC'")
    DoCmd.TransferText A_IMPORTDELIM, "cc_acct", "CC_ACC", F

    F = DLookup("Path", "tblFilenames", "[File]='CAMP'")
    DoCmd.TransferText A_IMPORTDELIM, "cc_camp", "CC_CAMP", F

    F = DLookup("Path", "tblFilenames", "[File]='CONT'")
    DoCmd.TransferText A_IMPORTDELIM, "cc_cont", "CC_CONT", F

    F = DLookup("Path", "tblFilenames", "[File]='SECT'")
    DoCmd.TransferText A_IMPORTDELIM, "cc_sect", "CC_SECT", F

Exit_Button58_Click:
    Exit Sub

Err_Button58_Click:
    MsgBox Error$
    Resume Exit_Button58_Click

End Sub
0
WillHudson
Asked:
WillHudson
  • 6
  • 4
  • 3
  • +1
3 Solutions
 
flavoCommented:
I'm not sure if this will work between 97 and 2000, but

In your 2000 version, File -> Get External Data -> Import -> Select 97 File -> Hit the Options Button -> De select Relationships and select Import/Export Specs (don't selet any tables/querys etc..).  Hit ok.

Dave
0
 
harfangCommented:
Hello,

The message says it all: "The text file specification 'cc_acct' does not exist." Somehow, they have not been part of the upgrade from '97 to 2000. You can try to import them from the old application -- File / Get External Data / Import, and them make sure you click "Import/Export Specs" from the "Options >>".

If that doesn't work, you will have to recreate them:
- Import the file "manually"
- set all needed parameters (delimiters, fields, etc.)
- save the specifications using the name "cc_acct" for the table CC_ACC.

I hope this helps,
(°v°)
0
 
NatchiketCommented:
It looks as though there was an import specification that was part of the orginal access 97 database that hasn't made it through to the new database.
You can try importing it File, Get external data, Import.  Select a copy of the original 97 database.  In the import objects dialog, click on options and then put a tick on Import/export specs (un tick relationships), then click on OK
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
WillHudsonAuthor Commented:
I no longer have the 97 version of the database, as the 97 version was useless it was deleted after convert.
Everything elase in the database works apart from this...
0
 
flavoCommented:
Well you're going to have to do it the long way as posted above.

dave
0
 
flavoCommented:
err.. Are you sure that was the answer?
0
 
NatchiketCommented:
lol ...
0
 
WillHudsonAuthor Commented:
Yes, i imported all the text files manually, saving the specifications with the correct names.
Clicking the button now to make it do it automatiacally works just fine.

Thanks!
0
 
harfangCommented:
Sure seems you just won the lottery, Natchiket ;)
(°v°)
0
 
flavoCommented:
Then I assume this post http#16369184 was correct, yes?
0
 
WillHudsonAuthor Commented:
Sorry guys, i don't know what i'm playing at today! I'll ask for the question to be reopened and split between the 3 of you. Thanks for your contributions!
0
 
NatchiketCommented:
maybe it's my lucky day, I'll try buying a scratchcard
0
 
harfangCommented:
I would say don't bother. No harm done, and Natchiket is happy to catch up a little ;)
(°v°)
0
 
flavoCommented:
>I'll try buying a scratchcard
Split it if you win :D

>I would say don't bother. No harm done, and Natchiket is happy to catch up a little ;)

I don't mind either, just checking

Dave
0
 
WillHudsonAuthor Commented:
Well seeing as you all contributed, and all gave valid answers within a short space of time, i'd rather reward all of you - after all you all spent your time to help
0
 
flavoCommented:
Thanks WillHudson, and thanks to papa smurf, I mean Mod too :P
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now