memory-problems with SQL-Query

W

Wolfgang

can you help me?

when i run this function,my mdb-File is 140MB bigger than before. i
can´t use the importXML - function, because i need the whole xml-File
in the cell. The xml-File, i have to import, is about 330kb. so i
can´t copy the whole file into the cell,like i did in the comment
(teststring) few lines after. so i have to do it step by step. so what
can i do, that the function is faster and/or my mdb-file becomes not
so big?


Public Sub GetXMLIntoCell()

Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim abfrage As String
Dim inhalt As String
'Dim teststring As String

Dim datleng As Long
Dim compvalue As Long
Dim zaehl As Long
Dim writerest As Boolean


date1 = Now()

writerest = False
zaehl = 40000
' 10000: + 400MB; 40sec
' 30000: + 150MB; 15sec
' 50000: + 100MB; 10sec
'max. 65400... else -> less memory

compvalue = 0

Set db = CurrentDb()

'clear all fields
abfrage = "UPDATE [_copy_DEF_BUSASSEMBLY] SET GSDFILE = ''" & " Where
(BUSASSEMBLYID > '46316' AND BUSASSEMBLYID < '46357');"
Set qry = db.CreateQueryDef("", abfrage)
qry.Execute

'TcTerminalsFile returns the path & name from my xml-file, that i have
to import
Open TcTerminalsFile() For Input As #1

datleng = FileLen(TcTerminalsFile())

Do While (Not EOF(1)) And writerest = False

If ((compvalue + (datleng Mod zaehl)) = datleng) Then
inhalt = Input(datleng Mod zaehl, #1)
abfrage = "UPDATE [_copy_DEF_BUSASSEMBLY] SET GSDFILE = (
GSDFILE & '" & inhalt & "')" & " Where (BUSASSEMBLYID > '46316' AND
BUSASSEMBLYID < '46357');"
Set qry = db.CreateQueryDef("", abfrage)
qry.Execute
'teststring = teststring & inhalt
abfrage = ""
inhalt = ""
writerest = True
Else
inhalt = Input(zaehl, #1)
abfrage = "UPDATE [_copy_DEF_BUSASSEMBLY] SET GSDFILE = (
GSDFILE & '" & inhalt & "')" & " Where (BUSASSEMBLYID > '46316' AND
BUSASSEMBLYID < '46357');"
Set qry = db.CreateQueryDef("", abfrage)
qry.Execute
'teststring = teststring & inhalt
abfrage = ""
inhalt = ""
compvalue = compvalue + zaehl
End If

Loop

Close #1

' abfrage = "UPDATE [_copy_DEF_BUSASSEMBLY] SET GSDFILE = (
GSDFILE & '" & teststring & "')" & " Where (BUSASSEMBLYID > '46316'
AND BUSASSEMBLYID < '46357');"
' Set qry = db.CreateQueryDef("", abfrage)
' qry.Execute


qry.Close
Set qry = Nothing


End Sub
 
Top