Importing text files Access 97

[erthman]

Importing text files using code (Docmd.transfertext using import specification). We are using Access 97.
We want to be able to run code in database (A) to import the text file into database (B)?

Does any one know how we can do this?

Erthman

 

[skfoster]

The easy way is using macro and then convert to code.

 

[erthman]

Hi Sherman thank you for comment!
But I still can not define where the table is under "Table Name". Because I am trying to run the Macro or Code from my Front office database and importing the file to the Back office database.

I am trying to avoid importing and exporting files in a multiple procedures!

 

[PaulF]

There may be other ways to do this, but I'm not aware of them, however, in the past I've done some data manipulation in other MDBs using code attached to the opening event of a form in that MDB, and used the code from the info below to open it. This code is some that I picked up from one of the websites out there, so I'm not sure who to give the credit to.

Open a form in an external database

(Q) How can I open forms present in an external database through Automation?

(A) Access 97 gives us a new method, OpenCurrentDatabase, a member of the Application object. The following code uses this method to get to a form in an external database.

'************ Code Start *************
'
Private Declare Function apiSetForegroundWindow Lib "user32" _
Alias "SetForegroundWindow" (ByVal hwnd As Long) As Long

Private Declare Function apiShowWindow Lib "user32" Alias "ShowWindow" (ByVal hwnd As Long, _
ByVal nCmdShow As Long) As Long

Private Const SW_MAXIMIZE = 3
Private Const SW_NORMAL = 1

Function fOpenRemoteForm(strMDB As String, _
strForm As String, _
Optional intView As Variant) _
As Boolean
Dim objAccess As Access.Application
Dim lngRet As Long

On Error GoTo fOpenRemoteForm_Err

If IsMissing(intView) Then intView = acViewNormal

If Len(Dir(strMDB)) > 0 Then
Set objAccess = New Access.Application
With objAccess
lngRet = apiSetForegroundWindow(.hWndAccessApp)
lngRet = apiShowWindow(.hWndAccessApp, SW_NORMAL)
'the first call to ShowWindow doesn't seem to do anything
lngRet = apiShowWindow(.hWndAccessApp, SW_NORMAL)
.OpenCurrentDatabase strMDB
.DoCmd.OpenForm strForm, intView
Do While Len(.CurrentDb.Name) > 0
DoEvents
Loop
End With
End If
fOpenRemoteForm_Exit:
On Error Resume Next
objAccess.Quit
Set objAccess = Nothing
Exit Function
fOpenRemoteForm_Err:
fOpenRemoteForm = False
Select Case Err.Number
Case 7866:
'mdb is already exclusively opened
MsgBox "The database you specified " & vbCrLf & strMDB & _
vbCrLf & "is currently open in exclusive mode. " & vbCrLf _
& vbCrLf & "Please reopen in shared mode and try again", _
vbExclamation + vbOKOnly, "Could not open database."
Case 2102:
'form doesn't exist
MsgBox "The Form '" & strForm & _
"' doesn't exist in the Database " _
& vbCrLf & strMDB, _
vbExclamation + vbOKOnly, "Form not found"
Case 7952:
'user closed mdb
fOpenRemoteForm = True
Case Else:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description, _
vbCritical + vbOKOnly, "Runtime error"
End Select
Resume fOpenRemoteForm_Exit
End Function
'************ Code End *************


HTH

PaulF