Transfer Database

[\karen scheu via AccessMonster.com\]

I have a series of macros that I want to convert to a VBA module. The first
action in the macro is TransferDatabase (Import an AS400 table to access
table.) How do I do this in VBA? I want to write to a log file as I go
along to keep track of what is going on? Also, use error handling which I
can't do through macros.

Can someone give me an example of how to code the connection and transfer?

Thanks,
Karen

 

[Alex Dybenko]

Hi,
in VBA you can use docmd.TransferDatabase method. Log file you have to
create by yourself, perhaps checking how many records were imported with a
query
docmd.TransferDatabase will raise an error if any, which you can handle with
standard VBA error handler On Error goto Proc_Err...

 

[John Nurick]

Hi Karen,

At the end of this message I've pasted a little VBA function that makes
it easy to write a string to a text file.

Add a wrapper procedure, maybe like this, which timestamps each entry:

Public Sub WriteToLog(S As String)
Const LOG_FILE = "C:\folder\file.txt"

WriteToFile Now() & vbTab & S & vbCrLf, LOG_FILE, False
End Sub

and then you can just use
WriteToLog "I wonder what happens here..."

I have a series of macros that I want to convert to a VBA module. The first
action in the macro is TransferDatabase (Import an AS400 table to access
table.) How do I do this in VBA? I want to write to a log file as I go
along to keep track of what is going on? Also, use error handling which I
can't do through macros.

Can someone give me an example of how to code the connection and transfer?

Public Function WriteToFile(Var As Variant, _
FileSpec As String, _
Optional Overwrite As Long = True) _
As Long
'Writes Var to a textfile as a string.
'Returns 0 if successful, an errorcode if not.

'Overwrite argument controls what happens
'if the target file already exists:
' -1 or True (default): overwrite it.
' 0 or False: append to it
' Any other value: abort.

Dim lngFN As Long

On Error GoTo Err_WriteToFile
lngFN = FreeFile()
Select Case Overwrite
Case -1 'True
Open FileSpec For Output As #lngFN
Case 0 'False
Open FileSpec For Append As #lngFN
Case Else
If Len(Dir(FileSpec)) > 0 Then
Err.Raise 58 'File already exists
Else
Open FileSpec For Output As #lngFN
End If
End Select
Print #lngFN, CStr(Nz(Var, ""));
Close #lngFN
WriteToFile = 0
Exit Function
Err_WriteToFile:
WriteToFile = Err.Number
End Function

 

[\karen scheu via AccessMonster.com\]

Thank you both for your replies. I created procedure that uses the docmd.
transferdatabase. It works perfectly. Thanks for the ideas for creating the
log file.