Solved

VB and Access (opening databases)

Posted on 1998-07-01
6
261 Views
Last Modified: 2006-11-17
I am connecting to an access database from visual basic.  when I click on the first command button, I export data from the access database.  when I click on command 2, to import it into another database I get an automation error, Here is the code for command1:
Private Sub Command1_Click()
Dim wrkJet2 As Workspace
Dim DBSPERF2 As Database
Dim dbsPubs2 As Database
Dim THEFILE As String
Dim CRCFILE As String
THEDATE = Month(Now()) - 1




If THEDATE = 4 Then THEFILE = "CMLAPP4.TXT"
If THEDATE = 5 Then THEFILE = "CMLAPP5.TXT"


If THEDATE = 5 Then CRCFILE = "CRCAPP5.TXT"

Dim MIDYEAR As String
Dim ext As String
ext = ".txt"
Set wrkJet2 = CreateWorkspace("", "admin", "", dbUseJet)
Set DBSPERF2 = wrkJet2.OpenDatabase("S:\RISK\APPS\APPSDATA.MDB")
Set OL = GetObject("S:\RISK\APPS\APPSDATA.MDB")
With OL
DoCmd.TransferText acExportDelim, "", "qappscmltocrmr", "c:\windows\desktop\" & THEFILE, True, ""

DoCmd.TransferText acExportDelim, "", "qappscrctocrmr", "c:\windows\desktop\" & CRCFILE, True, ""
'Set NEWDATABASE = Nothing
'Set UPDATEFCM = Nothing
Set OL = Nothing

Set NEWJET = Nothing
ACCESS.Quit


'DoCmd.TransferText acImportDelim, "", "qappscrctocrmr", "c:\windows\desktop\" & CRCFILE, True, ""

End With

End Sub
Here is command2
Private Sub Command2_Click()


Dim wrkJet2 As Workspace
Dim DBSPERF2 As Database
Dim dbsPubs2 As Database
Dim THEDATE As String
Dim CMLYYMM As String
THEDATE = Month(Now()) - 1


If THEDATE = 5 Then CMLYYMM = "cml9805.txt"

Set wrkJet2 = CreateWorkspace("", "admin", "", dbUseJet)
Set DBSPERF2 = wrkJet2.OpenDatabase("C:\WINDOWS\DESKTOP\JOHN.MDB")
Set al = GetObject("C:\WINDOWS\DESKTOP\JOHN.MDB")
With al
DoCmd.TransferText acImportDelim, "", "CML9705", "S:\RISK\CRMR\" & CMLYYMM, True, ""


End With

End Sub
The only way I can prevent this "automation error" is create a seperate Exe, does anyone know how to fix this?

thanks

0
Comment
Question by:thien
[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
6 Comments
 

Author Comment

by:thien
ID: 1976040
Edited text of question
0
 
LVL 4

Expert Comment

by:tomook
ID: 1976041
If you are using Access97, try changing these lines:
Set al = GetObject("C:\WINDOWS\DESKTOP\JOHN.MDB")
With al
  DoCmd.TransferText acImportDelim, "", "CML9705", "S:\RISK\CRMR\" & CMLYYMM, True, "" 
End With

To:
Set al = New Access.Application
al.OpenCurrentDatabase "C:\WINDOWS\DESKTOP\JOHN.MDB"
al.DoCmd.TransferText acImportDelim, "", "CML9705", "S:\RISK\CRMR\" & CMLYYMM, True, "" 

0
 
LVL 1

Expert Comment

by:daveko
ID: 1976042
A good practice to start getting into when dealing with data objects is to close and release the resources when you are done with them.  While it is true that Access will close resources for you as a general rule of thumb, if you leave it up to the system, you won't have control over when that cleanup really gets flushed from the system.

Try issuing the appropriate database and workspace close commands as well as setting the variables to nothing for good measure.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 1

Expert Comment

by:Sekans
ID: 1976043
Try using:
'In Command1
OL.Quit
Set OL = Nothing

'In Command2

AL.Quit
Set AL = Nothing
instead of Access.Quit

Regards,
Sekans
0
 
LVL 1

Expert Comment

by:CEBAKA
ID: 1976044
You can not declare the workspace with a button. You should do it in a module and if necesary :declare it public. The scope of your variable in your case is to small an will be lost after the click event.
This should be declared in a module :

Dim wrkJet2 As Workspace
    Dim DBSPERF2 As Database
    Dim dbsPubs2 As Database
0
 
LVL 4

Accepted Solution

by:
tomook earned 200 total points
ID: 1976045
Is this the question you wanted to give me points for, thien?
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

710 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