?
Solved

Any easy way to change an old Import/Export Specification

Posted on 2003-03-23
18
Medium Priority
?
669 Views
Last Modified: 2008-03-04
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

   


0
Comment
Question by:rberke
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 3
  • +1
18 Comments
 
LVL 2

Expert Comment

by:nschulz777
ID: 8190919
As far as I know it's the only way
0
 
LVL 5

Author Comment

by:rberke
ID: 8191279
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
 
LVL 2

Expert Comment

by:nschulz777
ID: 8192171
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 2

Expert Comment

by:nschulz777
ID: 8192181
Add these:
Close #1
rst1.close
Set dbs = nothing
0
 
LVL 5

Author Comment

by:rberke
ID: 8192576
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
 
LVL 2

Expert Comment

by:nschulz777
ID: 8192762
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
 
LVL 5

Author Comment

by:rberke
ID: 8193076
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
 
LVL 2

Expert Comment

by:nschulz777
ID: 8193424
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
 
LVL 5

Author Comment

by:rberke
ID: 8196505
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
 
LVL 2

Expert Comment

by:nschulz777
ID: 8197637
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
 
LVL 5

Author Comment

by:rberke
ID: 8207942
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
 
LVL 2

Expert Comment

by:nschulz777
ID: 8208252
Thanks Bob, best of luck with this question.
Norm
0
 

Accepted Solution

by:
Chmod earned 0 total points
ID: 8230083
PAQ'd & points refunded
http://www.experts-exchange.com/admin/adminShow.jsp?qid=20566946

Chmod
Community Support Moderator @Experts Exchange
0
 

Expert Comment

by:maynem31
ID: 8996387
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
 

Expert Comment

by:maynem31
ID: 8996452
Correction. TransferText does not bring up the dialog - it does the import programmatically.
0
 
LVL 5

Author Comment

by:rberke
ID: 8998777
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
 

Expert Comment

by:maynem31
ID: 9001257
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
 
LVL 5

Author Comment

by:rberke
ID: 9004509
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question