related records

C

Chriske911

can anyone give me a working example of an export of related tables

for now I use the following code:

Function CreateFCPSfile(Voyage As String, UVInbr As String,
ExportFolder As String, Exportname As String) As String
' create export file for customs based onn the imported data
Dim qdfManifest As DAO.QueryDef
Dim SearchBL As String
Set qdfManifest = CurrentDb.QueryDefs("qryfcps_manif")
qdfManifest.Parameters![VOY] = Voyage
qdfManifest.Parameters![POD] = "LIV"
Set rsManifest = qdfManifest.OpenRecordset
Set rsContainers = CurrentDb.OpenRecordset("tblContainers",
dbOpenDynaset)
Set rsGoods = CurrentDb.OpenRecordset("tblCargoDescription",
dbOpenDynaset)

Open ExportFolder & Exportname For Output As #1

Do Until rsManifest.EOF
SearchBL = "BL = '" & rsManifest.Fields("BL").Value & "'"
Do While True
rsContainers.FindFirst SearchBL
If rsContainers.NoMatch Then
Exit Do
Else
Call GoodsInContainer(UVInbr) ' print line
' Searching for other containers on same BL number
Do While True
rsContainers.FindNext SearchBL
If rsContainers.NoMatch Then
Exit Do
Else
Call GoodsInContainer(UVInbr) ' print line
End If
Loop
Exit Do
End If
Loop
rsManifest.MoveNext
Loop

rsManifest.Close
Set rsManifest = Nothing
qdfManifest.Close
Set qdfManifest = Nothing
rsContainers.Close
Set rsContainers = Nothing
rsGoods.Close
Set rsGoods = Nothing
Close #1
CreateFCPSfile = "File created on " & ExportFolder & "drive "

End Function

it's code I inherited from an existing app
but it's terribly slow and I am wondering if there is a better way to
do this without breaking functionality

grtz
 
G

Guest

Chriske911 said:
can anyone give me a working example of an export of related tables

for now I use the following code:
Function CreateFCPSfile(Voyage As String, UVInbr As String,
ExportFolder As String, Exportname As String) As String

----SNIP -----

As I understand it, .findFirst is relatively slow. I think it would be
faster if you used a query (SQL) to filter the table on "BL". That way your
record set will have only the records that match, not the whole table.

How many fields are necessary from "tblContainers"? If you only need a few,
specifying the fields instead of using "*" will also be faster (less data is
faster).

Using:
strContainersSQL = "Select field1, field2, BL From tblContainers"

would be faster than:

strContainersSQL = "Select * From tblContainers"


What does the Sub "GoodsInContainer(UVInbr)" do? It could also be slow.
Would you mind posting it?

Why is recordset "rsGoods" opened? I don't see where it is used.


The following code is AIR CODE...since I don't know the table structures or
data, I couldn't run any tests. (but it should work ;)

(and watch for line wrap...)

'****** Begin code ************
Function CreateFCPSfile(Voyage As String, UVInbr As String, ExportFolder As
String, Exportname As String) As String
On Error GoTo CreateFCPSfile_Err:

' create export file for customs based on the imported data
Dim qdfManifest As DAO.QueryDef
Dim SearchBL As String
Dim strSQL As String

Set qdfManifest = CurrentDb.QueryDefs("qryfcps_manif")
qdfManifest.Parameters![VOY] = Voyage
qdfManifest.Parameters![POD] = "LIV"
Set rsManifest = qdfManifest.OpenRecordset

If rsManifest.BOF And rsManifest.EOF Then
' no records found
rsManifest.Close
qdfManifest.Close
Set qdfManifest = Nothing
CreateFCPSfile = "No Records found"
Exit Function
End If

Set rsGoods = CurrentDb.OpenRecordset("tblCargoDescription",
dbOpenDynaset)

strContainersSQL = "Select * From tblContainers"
strContainersSQL = strContainersSQL & " Where BL = '" &
rsManifest.Fields("BL").Value & "'"
strContainersSQL = strContainersSQL & " Order by BL"

Open ExportFolder & Exportname For Output As #1

rsManifest.MoveFirst
Do Until rsManifest.EOF
Set rsContainers = CurrentDb.OpenRecordset(strContainersSQL,
dbOpenDynaset)
With rsContainers
If Not (.BOF And .EOF) Then
.MoveFirst
Do
Call GoodsInContainer(UVInbr) ' print line
.MoveNext
Loop While Not .EOF
End If
.Close
End With
rsManifest.MoveNext
Loop

Exit_CreateFCPSfile:
'close write file
Close #1
'return value
CreateFCPSfile = "File created on " & ExportFolder & "drive "
'close objects
rsManifest.Close
Set rsManifest = Nothing
qdfManifest.Close
Set qdfManifest = Nothing
rsGoods.Close
Set rsGoods = Nothing
'need this if rsContainers was closed above
On Error Resume Next
rsContainers.Close
Set rsContainers = Nothing
Exit Function

CreateFCPSfile_Err:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_CreateFCPSfile

End Function
'****** End code ************

HTH
 
C

Chriske911

Chriske911 said:
can anyone give me a working example of an export of related tables

for now I use the following code:
Function CreateFCPSfile(Voyage As String, UVInbr As String,
ExportFolder As String, Exportname As String) As String

----SNIP -----

As I understand it, .findFirst is relatively slow. I think it would be
faster if you used a query (SQL) to filter the table on "BL". That way your
record set will have only the records that match, not the whole table.

How many fields are necessary from "tblContainers"? If you only need a few,
specifying the fields instead of using "*" will also be faster (less data is
faster).

Using:
strContainersSQL = "Select field1, field2, BL From tblContainers"

would be faster than:

strContainersSQL = "Select * From tblContainers"

What does the Sub "GoodsInContainer(UVInbr)" do? It could also be slow.
Would you mind posting it?

Why is recordset "rsGoods" opened? I don't see where it is used.

The following code is AIR CODE...since I don't know the table structures or
data, I couldn't run any tests. (but it should work ;)

(and watch for line wrap...)

'****** Begin code ************
Function CreateFCPSfile(Voyage As String, UVInbr As String, ExportFolder As
String, Exportname As String) As String
On Error GoTo CreateFCPSfile_Err:

' create export file for customs based on the imported data
Dim qdfManifest As DAO.QueryDef
Dim SearchBL As String
Dim strSQL As String

Set qdfManifest = CurrentDb.QueryDefs("qryfcps_manif")
qdfManifest.Parameters![VOY] = Voyage
qdfManifest.Parameters![POD] = "LIV"
Set rsManifest = qdfManifest.OpenRecordset

If rsManifest.BOF And rsManifest.EOF Then
' no records found
rsManifest.Close
qdfManifest.Close
Set qdfManifest = Nothing
CreateFCPSfile = "No Records found"
Exit Function
End If

Set rsGoods = CurrentDb.OpenRecordset("tblCargoDescription",
dbOpenDynaset)

strContainersSQL = "Select * From tblContainers"
strContainersSQL = strContainersSQL & " Where BL = '" &
rsManifest.Fields("BL").Value & "'"
strContainersSQL = strContainersSQL & " Order by BL"

Open ExportFolder & Exportname For Output As #1

rsManifest.MoveFirst
Do Until rsManifest.EOF
Set rsContainers = CurrentDb.OpenRecordset(strContainersSQL,
dbOpenDynaset)
With rsContainers
If Not (.BOF And .EOF) Then
.MoveFirst
Do
Call GoodsInContainer(UVInbr) ' print line
.MoveNext
Loop While Not .EOF
End If
.Close
End With
rsManifest.MoveNext
Loop

Exit_CreateFCPSfile:
'close write file
Close #1
'return value
CreateFCPSfile = "File created on " & ExportFolder & "drive "
'close objects
rsManifest.Close
Set rsManifest = Nothing
qdfManifest.Close
Set qdfManifest = Nothing
rsGoods.Close
Set rsGoods = Nothing
'need this if rsContainers was closed above
On Error Resume Next
rsContainers.Close
Set rsContainers = Nothing
Exit Function

CreateFCPSfile_Err:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_CreateFCPSfile

End Function
'****** End code ************

HTH

thnx, I did just the thing what you propose here on my own
be it in a slightly other manner

I created another query with the required parameters instead of opening
the full container table

it is already way faster and it gives me the same results as far as I
can gather

I'll post code back later since I'm short in time right now

appreciate your input

grtz
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top