We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

VB and Access (opening databases)

thien
thien asked
on
Medium Priority
300 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

Comment
Watch Question

Author

Commented:
Edited text of question

Commented:
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, "" 

Commented:
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.

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

'In Command2

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

Regards,
Sekans

Commented:
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
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.