Solved

VB and Access (opening databases)

Posted on 1998-07-01
6
273 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
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!

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

630 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