ERROR #3704: Operation is not allowed when the object is closed. Microsoft Access 308: Import, Export, Specification, TransferText, TransferSpreadsheet, Split Text
 

4/24/2017: You may see an "operation not allowed" error on the site. We're working on fixing the problem. Nothing seems to be affected, it's just annoying. Carry on. :)   [dismiss]
 
Access 2007-2013
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NEW Release: Access Advanced Level 4 and Level 5   dismiss
 
 
Courses - Microsoft Access 308
Description: Advanced Access
Running Time: 94 minutes
Pre-Requisites: Access 307 very strongly recommended
Previous Lesson: Access 307
Next Lesson: Access 309
Main Topics: Import, Export, Specification, TransferText, TransferSpreadsheet, Split Text
Versions: This course is valid for Access 2000 through 2003. If you are using Access 2007 or 2010, you will still benefit from this course. You will find that the concepts are the same, but there are cosmetic differences between the versions.

    

Order before 4/27/2017 to get a FREE upgrade to our Access 2010 version!
Click here for details

 

Access 308 continues our advanced Access database development with VBA programming.

This class focuses on importing and exporting data. We'll learn about the different types of importing and exporting, and what it's used for. You will see how to export tables to another Access database, how to export query data to Microsoft Excel, and how to use the OfficeLink button to export data to both Excel and Word.

We don't stop there. That's just the easy stuff.

Next, we'll learn about comma delimited and fixed-width text files. How to create an import/export specification; how to create a macro to export text automatically using the TransferText action. We'll then learn about a similar TransferSpreadsheet macro action.

 

You will then learn how to import data from another Access database; how to append imported records to an existing table; how to link to tables in another database; how to link to an existing Excel spreadsheet; how to link to a Named Range in Excel; and how to link to a text file.

 

Finally, I'll show you a neat trick - how to take a name like "Joe Smith" and split it into two fields "Joe" and "Smith."


 


ACCESS 308

Lesson 1.
- Working with External Data
- ODBC (Open DataBase Connectivity)
- Importing & Exporting Data
- Linking v. Importing
- Examples

Lesson 2.
- Exporting to another Access database
- Creating a Blank Sales Rep database
- Exporting Customer Table
- Exporting to an Excel Spreadsheet file
- Creating a query to limit our data
- Exporting the query data to Excel
- Using the OfficeLink button
- Analyze It with Microsoft Excel
- Publish It with Microsoft Word

Lesson 3.
- EXport Text: Comma Delimited
- Export Text: Fixed Width
- Create EXport Specification
- Create a Macro to Export Text
- TransferText Action
- Create a Macro to Export to Excel
- TransferSpreadsheet Action

Lesson 4.
- Import From Another Database
- Appending Records To Existing Table
- Matching Field Names (Append Query)
- Creating Values For Unmatched Fields
- Import From an Excel Spreadsheet
- Import Wizard

Lesson 5.
- Importing Delimited Text File
- Importing Fixed Width Text File
- Fields That Are Directly Adjacent
- Linking To Tables in Another Database
- Linking To A Spreadsheet
- Linking To A Named Range in Excel
- Linking To A Text File

Lesson 6.
- Splitting Single Name Field into Two
- INSTR Function
- LEFT and RIGHT Functions
- LEN Function
 

 


 
 


Try a FREE Demo Lesson

 
 
 

Student Interaction: Microsoft Access 308

Richard on 1/1/2007:  External Data, Importing & Exporting Tables, from Excel, Text Files, Linking Tables, Spreadsheets, Splitting Names
Shirley on 4/24/2008: Export to a user-specified location: Access 308 is a great tutorial for automating the export of data to a txt or alternate file, however, it only provides the ability to export to a statically specified file. Is there a way to have Access export to a location that is specified on a field on another form. I have been playing around with this, both in vba and macros and can't get it to work. For example, if the form that also contains the button for executing the macro or vba code also had an unbound field that the user could specify the location and file name to export to, I would like to reference that field as the file location. I have tired just about every syntax I can think of and can't get it to work. This would allow the user to save the file in any location or file name. Also, by doing this, I could set it up so that the user could use the file browser to specify the location. Thanks for your advice and I love your tutorials.
Richard Rost on 4/24/2008: Shirley, EXCELLENT question.

Yes, it is possible to have the user specify the filename for the export. There are two EASY ways to do it. The first is to simply place a textbox on the form where your export button is, and have the filename in that.

Let's say that you create a textbox called ExportFilename on your SecondaryMenuF form right below your button. Now, in the FILENAME parameter for the macro that performs the export, just put:

=[Forms]![SecondaryMenuF]![ExportFilename]

We learned about this technique in Access 202, lesson 2, where we shared values between two forms. You can use this same notation in macros, queries, VBA code, etc.

The other way you could do it, would be to use the InputBox command to prompt the user for the filename when the macro runs. Here's the syntax:

=InputBox(Prompt,WindowTitle,DefaultValue)

So in this case, you would say:

=InputBox("Export Filename","Export","C:\ExportFile.xls")

And that would prompt the user for the filename, giving them a default vallue of C:\ExportFile.xls. We learned about InputBox in Access 306.

There is another way you could do it... but that would involve introducing the FILE and FOLDER controls to your database. I personally don't like pulling in extra controls into my database unless absolutely necessary, but Microsoft does make nice controls that you can browse for files and folders with. I cover them in my Visual Basic 106 class, and the same controls work in Access too.

You're still going to have to refer to the filename you select in your macro though - so you'll still need the Forms!FormName!Field notation.

ibad on 1/20/2009: i have form in this form i subform1 and subform2 if i click subform1 the related value in subform2 should be shown . it can be done with requrey but i need mdb file with example
Richard Rost on 1/21/2009: Ibad, you need to link the child and master properties correctly in the subform, and everything should work automatically. I cover this in Access 202.
Marina  Stein on 3/25/2009: Hi Richard,
I just ordered Access 3008 course. I really need workbook for this class. Please let me know why don't I see this book in your file.
Thank you, Marina

Richard Rost on 4/1/2009: Marina, I don't have handbooks available for ALL of my classes. It's based mostly on demand. If I don't sell a lot of handbooks for Access 307, I won't bother writing one for 308. If anyone else is interested in an Access 308 handbook, please post a note here and I'll consider it. Thanks.
 Carl Kowalski on 8/9/2012: I would like to be able to have a link in a rocord to open a pdf file. Is this possilble in access 2007
Alex Hedley on 8/12/2012: Hi Carl,

There are a couple of methods you could use.

How are your VBA skills?

1 is to create a button and add this code:

Dim strPath as String
strPath="C:\Path to the PDF document"
Application.FollowHyperLink strPath

You could replace strPath with a Field on your Form, it would have to contain the full path though.

Shorthand would be just
Application.FollowHyperlink "C:\path\FileToOpen.pdf"

--

Another option is using the Shell

strPath = "C:\path\FileToOpen.pdf"
Set shell = CreateObject("WScript.Shell")
shell.Run Chr(34) & strPath & Chr(34), 1, False

To be on the safe side I've added Chr(34) (which is "- double quotes) around the file name in case there are spaces in folder names, this causes issues and the Shell will stop at a space so won't find that full destination.

--

Another option is 'ShellExecute', add this to your Form's code:

Option Explicit
Option Compare Database
 
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
 
Private Const SW_SHOWNORMAL As Long = 1
Private Const SW_SHOWMAXIMIZED As Long = 3
 
'(in the button click event you have chosen:)
 
Private Sub Command1_Click()

Dim strPath as String
strPath="C:\Path to the PDF document"

    ShellExecute 0, "Open", strPath, vbNullString, "C:\", SW_SHOWNORMAL
End Sub

Regards
Alex

Panagiotis Zikos on 12/16/2012: Hi Richard. I have found your free lessonsn and tips and ticjs very helpfoyll for me. I am a doctor and I have costructed a data base fos the exams of my patients. I would like to reverse the columns wand row so to have in the first row left the names of each exam and the results in vertical orientation havig as a column header the dates of exms done. Thank you in advance.

Reply from Richard Rost:

This is surprisingly not that easy to do in Access. In Excel you can flip the axes easily with one click, but Access is really designed so that RECORDS go across in ROWS and FIELDS go down in COLUMNS. You could make a custom REPORT to display records this way using columns (like mailing labels), but it's not something that Access can do by itself.

Panagiotis ZIkos on 12/18/2012: Thank you for your immediate response. I also was exciting for the fact that access cann't do it. I didn't know it. I don't remember very well but I think that a programmer who hadndesigned an database for us in the past, had made it. There's was a table with five fields. Examid, exam set, examshow ( yes/no), and exam name. I could never found the way this table works. Any way thank you.
Panagiotis ZIkos on 12/19/2012: Another comment/question. I found the answer concerning the path to open pdf file very interesting. My question is :can I open a specific excel file/worksheet where I have storedmthendatanof my patients in anyway I want to do. (Vertiical columns each exam row the dates ?)

Reply from Richard Rost:

Yes, it's possible to load an Excel file using VBA. I don't have the code handy, but I'm sure you can find it easily with a Google search.

Randy Timmerman on 3/2/2013: Hi Richard. Great classes...I have Access 2010. I'm in the process of making sport card database. I want to import old checklist and info from excel spreadsheets I have. I import them. the information is updated in my table. when I go to run queries (previously constructed before the import) and they don't find the information I'm wanting. For instance, I want it to find a Topps card I insert via import and it will not pull it up. when I go to the table, it is present but will not pull up in the query. Is there a reason after import that you have to remake queries everytime? (one note when I remake the query it works!) Thank you Randy

Reply from Richard Rost:

I've never seen that happen before. Sure, you have to close and re-run the query after importing new data, but you shouldn't have to rebuild the query from scratch. I'd need to see your database to tell you for sure what's going on.

Randy Timmerman on 3/2/2013: I will mess with it some more before I waste your time. If it doesn't work I will get with you.
thank you Randy

Randy T on 3/3/2013: I tried different things and can't get it to work. Do you mind to look at my database? If so how do you want it sent.

Reply from Richard Rost:

You can send me your database and I'll look at it when I get a few minutes. Instructions for submitting files are on my TechHelp page.

Randy Timmerman on 3/4/2013: Thanks for your help. I figured it out. Sorry for the trouble.

Reply from Richard Rost:

It's no trouble at all. That's why we're all here... to help each other.

Joni Moore on 8/20/2013: Richard... is there a way in this process to make sure you're not importing duplicate data?

Reply from Richard Rost:

There are ways to check for duplicate data. The best way is to INDEX your table based on some field that has to be unique (like an ID or SSN).

Richard Lanoue on 2/29/2016: I have a Vb code version I like... But is there a way to automatically adjust column width, highlight headings and format data in the column (ex. (800)123-4567... I don't want it to be 8001234567 or $100,000,000 NOT 100000000)... Is it possible and if so how?

Reply from Alex Hedley:

You can add a Format to your fields

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP