How do I create an import/export specification? 4

[EdRev]

I searched the ACCESS Forum for this subject and found some threads that are quite helpful. I followed them but got lost in the process.
This is how I did it. I open my Access database(Access 97) and on the file menu, select Get External DAta ... then Import. I open the same database and select one of the tables. I clicked Options and click on the checkbox Import/Export Specs and clicked on option button Definition only and then OK. The table got copied but this is here where I got lost. What's the name of the the import/export specification that was supposedly been created when I import the table.

Any help will be greatly appreciated.

 

[lameid]

You get Import and export specifications from creating text files. I don't think any other file type has them. Access tables do not require specifications to transfer...

File
Get External Table
Files of type := Text Files
Browse to text file and hit import
Anytime using the wizard to create a spec, hit the advanced button and you can modify the configuration manually and save the spec using Save As. You can also hit advanced immediately to open a previously saved spec instead of setting it up again (Specs button).

 

[EdRev]

I got lost on this "hit the advanced button and you can modify the configuration manually and save the spec using Save As. You can also hit advanced immediately to open a previously saved spec instead of setting it up again (Specs button)". When I hit the advance button, it takes me to the Advanced Find Window, there was no option to modufy and save the spec.

thanks,

 

[Maquis]

Hi Ed,

I work with file specs all the time. They come in very handy for fixed width files, but you're right, the Access menus are not very straightforward when it comes to creating and modifying these things.

Here's how to create/save a spec:

1. File Menu ---> Get External Data ---> Import
2. Pick a text file, any text file and click "Import"
3. When the Import Text Wizard pops up, click "Advanced"
4. Either start designing your spec from scratch here or click "specs" to load a saved spec.
5. When you've finished designing your spec click "Save As" and save it.


You can now cancel out of your import and your spec will still be saved for future use. You can reference the spec in a macro or a docmd.transfertext command in VB.

Maq B-)
<insert witty signature here>

 

[ErikZ]

This should be in the FAQ

 

[jahlmer]

Hey Maq,

When you say that you can reference the spec in a macro, I got excited. I went to add a new macro, but the search (or spec) is not in the list of available options. Do you know where I would make this into a macro?

Also, when answering questions about things like if the first row is the header labels, name, etc, is there a way to have that automate (since the file format will never change in my situation)?

Thanks you guys.

 

[Maquis]

Jahlmer,
To use in a macro just put transfertext in the action field and the box below will have a place for file name, spec, table, etc.

For your 2nd question, nope, I don't know an easy way to type all that stuff in, but that is basically why you are creating a saved spec in the first place. Once you type it all in and save it you don't have to set it up again, it's there forever. (forever = 1 to 2 years in computer world) Maq B-)
<insert witty signature here>

 

[EdRev]

thanks all for your suggestions. I followed what Maq suggested and it worked. The only question I have now is where I cancopy this import specification. I'm also working on this project at home and I need to copy this specification into my home.pc

thanks again,

 

[jahlmer]

Hey, cool. One more quick brain picker from me on this topic.

Lets say I have a directory that, every couple of days, a new file is added to the directory that must be imported into the same table each time(actually to replace the existing records.

Using a macro to import the spreadsheet, can I impliment a search like you can for a text import? What I would do if I COULD, would be to use the age of the file to be less that 2 days, and that would ensure it would be the newest file that was being imported, then I wouldn't have to rename the file each time to make sure the macro worked.

 

[Maquis]

Ed,
E

http://wwwwh,

that's a toughy. You can move a spec from database to database, but I don't think you can save a spec externally from a database.

Here's what I would do.
1. Create a blank database
2. In the blank database goto File --> Get External Data --> Import.
3. Pick the database with the spec from the list
4. Click &quot;options&quot; (last button).
5. Check Import/Export specs.

You should now have a totally blank database containing only your specs. Hopefully it's tiny enough to fit on a floppy which you can carry home.



Jahlmer,
Sorry, but I have no clue how to search for files. Perhaps some brilliant lurker can help you there.

Normally, my users just know when a new file is available and they then open the database and click a button to import the file. I don't use macros very often, so my buttons will usually run VB code which prompts the user for the file path and name and then executes a transferText command to import the file they entered. You can reference the specification in the transfer text command, so once you write the VB code it maintains itself.

If you're unfamiliar with writing code, take a look at the InputBox command and the docmd.TransferText or docmd.TransferSpreadsheet command in Access's help file. There should be some good examples there to get you started. Maq B-)
<insert witty signature here>

 

[THoey]

Ed, I believe (?) that the spec is saved as part of the database. To have it on your home machine, you would need to copy the MDB to that machine or recreate it this one time. Terry M. Hoey

 

[lameid]

Sorry, I didn't check back sooner...
If you use VBA you should look into using the FileSearch object to manage your imports. Here is some code I wrote with some segments deleted for proprietary reasons... The example below imports only a fixed width file. I added comments so you could get a feel for what it does.

Function importfiles(strPath As String, strType As String)
'Look in directory strPath for file
'import files of type strType
'use select case construct to handle different file types
'This is necessary if you have text files of different layouts with different extenstions
'Uses Name statement to move file to the Imported subdirectory after import

Dim fs As Object
Dim i As Integer
Dim db As Database, sqlstr As String
Dim strDest As String, strOrig As String
Set db = CurrentDb
Set fs = Application.FileSearch
DoCmd.Hourglass True

If Right(strPath, 1) <> &quot;\&quot; Then
strPath = strPath & &quot;\&quot;
End If

With fs
.newsearch
.LookIn = strPath
.FileName = &quot;*.&quot; & strType
If .Execute > 0 Then
'MsgBox &quot;There were &quot; & .FoundFiles.Count & _
&quot; file(s) found.&quot;

For i = 1 To .FoundFiles.Count
Debug.Print .FoundFiles(i)
strOrig = .FoundFiles(i)
Forms![frmImportExport]![txtFilename] = Right(strOrig, (Len(strOrig) - Len(strPath)))
strDest = strPath & &quot;Imported\&quot; & Forms![frmImportExport]![txtFilename]
Select Case strType
Case &quot;TXT&quot;
DoCmd.TransferText acImportFixed, &quot;Text file Spec&quot;, &quot;Import Table&quot;, .FoundFiles(i), False
Name strOrig As strDest
Case Else
MsgBox &quot;The File type you entered is not supported.&quot;, vbInformation
End Select
Next i
Else
MsgBox &quot;There were no files found.&quot;
End If
End With
DoCmd.Hourglass False
End Function

 

[lameid]

Something else to mention...
This code uses a form named &quot;frmImportExport&quot;
with a text box named &quot;txtFilename&quot; on it.

It was written this way because after import, the data had to be manipulated. The code would run a query to put the filename imported in a filename field where Filename was null and run addition queries off the data all referencing the control on the form. I thought this would be easier to manage than putting the literal SQL statements in code and concatenating the criteria together. In my situation, the file names all referenced the date and capturing the file name was useful.

 

[EdRev]

Thanks again guys, you all have been such great help.

I haven't gotten the chance to log-in until now. I will try all the suggestions you offered. But if all else failed, I guess you'll be hearing from me.

In the meantime, can anybody help me with my problem about printing report. I have a vb form that gets populated with a query and I can navigate forward and backward. On this form I have a print button that's supposed to print only the current record. When I tried to print, all the records gets printed.

Can any of you guys help me with this.

Again, thanks.