Export access table to notepad

[Udd.]

I'm after some help please. I'm trying though excel to open up access and
export a table to notepad. Having never done anything like this i'm
struggling. I've mashed bits of code together that i've found on here but i'm
still missing something. Can you look at the code below and point out where
i'm going wrong or even suggest a better way?

Sub access()
Dim ac As Object
On Error Resume Next

Set ac = GetObject(, "Access.Application")

If ac Is Nothing Then
Set ac = GetObject("", "Access.Application")
ac.OpenCurrentDatabase "k:\warehouse\lm's\test.mdb"
Else
AppActivate "Microsoft Access"
End If

DoCmd.TransferText acExportDelim, , "tbl_main", "E:\test.txt"
ac.Quit

End Sub

Many thanks,

Richard.

 

[OssieMac]

Hi Richard,

Try the following:-

Sub access()
Dim ac As Object
Const acExportDelim = 2 'Required with late binding

On Error Resume Next
'Try GetObject first in case Access already open
Set ac = GetObject(, "Access.Application")

If Err.Number > 0 Then
'Error is returned by GetObject if Access not
'already open so use CreateObject
On Error GoTo 0 'Reset error trapping ASAP
Set ac = CreateObject("Access.Application")
End If

ac.OpenCurrentDatabase "k:\warehouse\lm's\test.mdb"

'ac.Visible = True
'Can only use following line if Access is visible
'but there is no need to make it visible.
'AppActivate "Microsoft Access"

ac.DoCmd.TransferText acExportDelim, , "tbl_main", "E:\test.txt"

ac.Quit

Set ac = Nothing

End Sub

 

[Udd.]

Thank you very much Ossie.
I noticed after I first posted that i'd missed the
"ac." from the fron of DoCmd.TransferText acExportDelim, , "tbl_main",
"E:\test.txt"
Thanks for your help and correcting my mashed code @

 

[Udd.]

All of my code is now fully automated except for when access first loads when
i'm presented with the screen "do you want to....open, cancel etc"
If I had this line in

ac.OpenCurrentDatabase "K:\Operations\JEAN\Lean Pick Info\11.
Nov\november.mdb;Persist Security Info=False"

it disables the message but also prevents the exportation of the table to
notepad - is there a way around this?

Thanks,

Richard.

 

[OssieMac]

I don't get the message so I am not sure what it is all about. What version
of Access are you using? I tested it on Access 2002.

I suggest that you post as a separate question and perhaps also on the
Access Programming site.

As an afterthought, try making Access visible (see my commented code in
previous post) and then exit the sub and see whether the required database is
in fact loaded.