• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

VB and Access (opening databases)

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
thien
Asked:
thien
1 Solution
 
thienAuthor Commented:
Edited text of question
0
 
tomookCommented:
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
 
davekoCommented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
SekansCommented:
Try using:
'In Command1
OL.Quit
Set OL = Nothing

'In Command2

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

Regards,
Sekans
0
 
CEBAKACommented:
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
 
tomookCommented:
Is this the question you wanted to give me points for, thien?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now