Solved

VB and Access (opening databases)

Posted on 1998-07-01
6
222 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
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now