Any easy way to change an old Import/Export Specification

A week ago I created an Import/Export Specification named "my2003Spec".  Now I want to change it.

After an hour of searching, I came up with an ugly way of doing it. (I also learned how to view the msysIMEXspecs table but I don't want to maintain the table myself).

Does anyone know how to simplify steps 5, 6 and 7 below.  I find they are a huge waste of time.

I am using Access XP

Bob


TO FOLLOW IN MY FOOTSTEPS, CREATE A SPEC CALLED my2003Spec
1.     Select menus File/Get External Data/Import
2.     Browse and select any comma delimited text file.  I'll call it FILE2003
3.     Click Import/Advanced/Save As/my2003Spec
4.     Cancel out of the wizard and get back to access tables

NOW, LET'S PRETEND A WEEK HAS GONE BY AND SOMEONE HAS DELETED FILE2003.

HERE IS MY UGLY WAY TO CHANGE MY2003SPEC.  
5.     Select menus File/Get External Data/Import
6.     Browse and select ANY comma delimited file (This is the ugly part, I hate browsing for a totally unrelated file.)
7.     Click Import/Advanced/Specs
8.     In the drop down box, select my2003Spec and click Open
9.     Make the desired changes and click Save As
10.     Save As my2003Spec and respond Yes to the overwrite existing spec message
11.     You're done, so cancel out of the wizard

   


LVL 5
rberkeConsultantAsked:
Who is Participating?
 
ChmodConnect With a Mentor Commented:
PAQ'd & points refunded
http://www.experts-exchange.com/admin/adminShow.jsp?qid=20566946

Chmod
Community Support Moderator @Experts Exchange
0
 
nschulz777Commented:
As far as I know it's the only way
0
 
rberkeConsultantAuthor Commented:
Anybody got an idea about how to make the dialog pop up in a visual basic program?  I could then put it on my own menu.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
nschulz777Commented:
Import Example for importing a 3 column delimited file using DAO, you need to add a reference to DAO 3.6. The import path/file name is in a textbox called TxtFileName
Dim StrFileName as string
StrFilename = Me!TxtFileName
Dim strdocname as string
Dim StrField1 as string
Dim StrField2 as string
Dim StrField3 as string
Dim dbs as database
Set dbs = CurrentDb()
Dim rst1 as dao.recordset
strdocname = "TableName"
Set rst1 = dbs.OpenRecordset(strdocname, dbOpenDynaset)
Open StrFilename For Input As #1
Do While Not EOF(1)
Input #1, StrField1, StrField2, StrField3
rst1.AddNew
rst1![Field1] = StrField1
rst1![Field2] = StrField2
rst1![Field3] = StrField3
rst1.Update
Loop
0
 
nschulz777Commented:
Add these:
Close #1
rst1.close
Set dbs = nothing
0
 
rberkeConsultantAuthor Commented:
nschulz777 sorry, good answer, but to someone else's question:)  

I'm looking for code that will open the Import/Export Specification dialog box.  (Frankly, the actual import is not of interest to me.)

Your code imports a file and acutall SKIPS the Import/Export specification.  (In other words I want to automate steps 5, 6 and 7).

Thanks anyhow, I appreciate your attempt to help.

Bob
0
 
nschulz777Commented:
Can't find any way to open the dialog, would need some kind of a dll call. The spec info is stored in tables MSysIMEXColumns and MSysIMEXSpecs. (tools - options => check system objects to see them)
You could write specs on the fly with your own dialog and no msaccess dialogs at all just by modifying the table with code.
Would be some experimenting involved but might be worth it.
0
 
rberkeConsultantAuthor Commented:
I've never mastered VB (O.K. I've never even minored it), so forgive the following ramble.

Aren't there dialog boxes for just about everything that you get from the Access menus?  For instance, the following should display a saveas dialog.

   Dim dlgSaveAs As FileDialog

   Set dlgSaveAs = Application.FileDialog( _
    FileDialogType:=msoFileDialogSaveAs)

   dlgSaveAs.Show

So, I suspect similar code can display an Import/Export Specification dialog box.  

All I've got to do is change the magic words to other magic words. But, I am really weak at finding magic words. I tried typing in "Application." in VBA. When I type the period, a drop down list appears showing 100 properties/methods of an application.  I looked for the word "dialog".  But this is haphazard.  

So, I'm about ready to "take NO for an answer".

 

On a side note, years ago I was pretty comfortable with Access 2.0. I wrote my own spec management programs that went at the msys tables. It started off easy, because I stuck to strings. But then I got ambitios and there was always "one more thing" to do. Probably spent 40 hours on it and never generated more that 20 specs, so it was a negative productivity tool. Then, along came an access upgrade, and everything stopped working. Then, another upgrade came along and virtually everything I knew about VBA became obsolute. Very discouraging. I am now taking baby steps and trying to learn again. Until I get good, I'll just stick to Bill Gates version of Import/Export specifications.
0
 
nschulz777Commented:
I'm at a loss finding any kind of code reference to the dialog. The stucture should be along the lines of Application Object => Current Data Properties => All Stored Proceedures but I hit a dead end. MSDN just repeats the help. I wouldn't give up yet, see if anyone else has hit on how to do this. I think one thing you can do is transfer specifications from one db to another using the transferdatabase method selecting acstoredproceedures. I gave up on access when 2000 hit and went to VB6. Ther alot more controls available and you can do anything with a database using DAO or ADO. In VB6 it's very easy to set up custom dialogs where the user selects the delimiter, columns etc. for export.
0
 
rberkeConsultantAuthor Commented:
nschulz777

You say you "gave up on VBA and went with VB6."  I thought they were nearly the same language.  If I switch to VB6, how well does it integrate into Access?  For instance, does the code and its source still reside in the MDB?  

By the way, I have to put this on hold a few days. I've got real work to do, and my bosses (me, myself and I), doesn't classify "learning neat tricks" as real work until they start bringing in money.

I'll probably not respond to this thread till Wednesday.

Bob



0
 
nschulz777Commented:
Hi Bob:
No, the code resides in the .exe file you create. I don't use ado, I use dao 3.6. In dao code there are a few minor changes but for the most part the code is the same. All the accmd... stuff is not there. Another example is currentdb(), can't use it. All the SQL power is there. I display the data in the flexgrid control which has advantages of being able to color alternate rows or individual cells. You can also talk to word, excel, autocad, create your own active-x controls or dlls. I got my copy of VB6 from ebay for 200 bucks. Here's a sample of a vb6 function which opens the database and sets the allowzerolength property on all fields, should look fairly familiar codewise:

Function ModifyZeroLength()
Dim dbs As Database
Dim tdf As TableDef
Dim fld As Field
Dim strfile As String
strfile = DataTransUtil.DestT
Set dbs = OpenDatabase(strfile)
Set tdf = dbs.TableDefs(DataTransUtil.DestCB)
For Each fld In tdf.Fields
If fld.Type = dbText Then
fld.AllowZeroLength = True
ElseIf fld.Type = dbMemo Then
fld.AllowZeroLength = True
End If
Next fld
Set dbs = Nothing
End Function
0
 
rberkeConsultantAuthor Commented:
Obviously, we have made no headway at all on my original question.  

The side issues were pretty far off the mark, but still worth some points, (particularly your remarks about vb.) So I'll open a Points for nschulz777 question in this TA.

But, I'm going to leave this qustion open in the hopes that it will eventually be answered.  

Bob

0
 
nschulz777Commented:
Thanks Bob, best of luck with this question.
Norm
0
 
maynem31Commented:
Bob,
I'm a newbie so be gentle with me.
I'm looking to automate import/linking in a similar way to yourself. I'm using Access 2002 and have come across the following 2 ways to get the Import /link dialog box up (programatically):

1. DoCmd.TransferText - good control over the format of the file to import

2. DoCmd.RunCommand( acCmdLinkTables ) - Invoked the File > Get External Data > Link Tables menu option and hence the dialog. You can also use the acCmdImport constant to invoke the Import dialog.

Hope this is what you were/are looking for.

Michael
0
 
maynem31Commented:
Correction. TransferText does not bring up the dialog - it does the import programmatically.
0
 
rberkeConsultantAuthor Commented:
DoCmd.RunCommand( acCmdLinkTables ) takes me to the "Link" dialog box.

Ideally, I want something that will take me to the "Import/Export Specifications" dialog box.
I would also accept something that takes me to the "xxxx Import Specification" dialog box.

Thanks for trying.  I've just learned to live with doing steps 1 through 8 once ever few weeks.

Bob
0
 
maynem31Commented:
My mistake - I've been working with linking tables.
You can use the acCmdImport constant to take you to the Import dialog.
Look at the RunCommand method help in the Programming in Visual Basic > Microsoft Access Visual Basic Reference part of the Access 2002 help file. If you expand the acCommand parameter you will get a list of all the constants you can use with RunCommand i.e. all the menu actions you can invoke.
You can then just about work out which constant applies to which menu option, although there isn't a direct correspendence - the commands are not named by their positions in the menu hierarchy.

Hope this helps.
Michael
0
 
rberkeConsultantAuthor Commented:
DoCmd.RunCommand (acCmdImport) does, indeed, automate step 5 from my first post on 3/23.

but, what I wanted was a way to run steps 5, 6, 7 and open the drop down box of step 8 all with a single click.


Thanks for trying.

If you enjoy this kind of playing, by all means keep trying to help.

But, this is low on my long list of things I wished I could do, so don't spend too much time trying.

Bob
0
All Courses

From novice to tech pro — start learning today.