:strip_exif()/u/29711/tweakers.gif?f=community)
De situatie is als volgt: Access database die door velen gebruikt wordt, zonder permanente internetverbinding en waarbij een MySQL-server als uitwisselpunt moet dienen. Hiervoor is op iedere pc de MyODBC-software geinstalleerd.
Tot voor kort gebruikte ik voor de uitwisseling van gegevens van Access naar MySQL de TranferDatabase methode. Dit had een enorm nadeel, namelijk dat het zeer traag was. Bovendien raakte je gegevens van de structuur van de tabellen in Access kwijt, aangezien bepaalde zaken (zoals een default value) blijkbaar verdwijnen nadat je ze gedownload hebt.
Anyway, voor het up- en downloaden heb ik nu twee subs geschreven die prima werken. Het idee is heel simpel: in plaats van alle structuur downloaden (wat TranserDatabase doet), worden nu nog slechts gegevens gedownload danwel geupload.
Overigens is deze code natuurlijk prima te gebruiken voor iedereen die gegevens moet uitwisselen met een MySQL-server...
Wat slordig commentaar erbij en wellicht kan het nog wel wat sneller, maar goed, het werkt alvast. Deze sub veronderstelt dus dat de lokale tabel leeg is.
En de sub voor het uploaden....
Tot zover het algemene deel. Zoals al wel te zien is vereisen beide subs een lege doeltabel. Dus hetzij lokaal, hetzij op de server moet eerst de tabel worden leeggemaakt. En een backup is natuurlijk ook altijd wel fijn, je weet immers nooit. Vandaar onderstaande code.
Waar ik nu tegenaan loop is het volgende: het uploaden is veel trager dan het downloaden.
Niet het uploaden zelf lijkt de oorzaak, maar het maken van een online backup.
Het maken van die backup is op zichzelf een handeling die nog geen seconden hoeft te kosten (via bijv. PhpMyAdmin), via Access duurt het opeens veel langer. Uploaden volgens onderstaande code duurt dan ook wel 3x zo lang als downloaden.
Ziet iemand waar het misgaat?
Op zich een heel eenvoudige sub. Waarom moet dit gebeuren nu zo enorm veel langer duren dan het downloaden van de gegevens? Daar maak ik immers ook een lokale backup.
Met name deze regels kunnen online in hooguit twee seconden gebeurd zijn:
Wat moet ik omgooien, waar gaat het mis?
Tot voor kort gebruikte ik voor de uitwisseling van gegevens van Access naar MySQL de TranferDatabase methode. Dit had een enorm nadeel, namelijk dat het zeer traag was. Bovendien raakte je gegevens van de structuur van de tabellen in Access kwijt, aangezien bepaalde zaken (zoals een default value) blijkbaar verdwijnen nadat je ze gedownload hebt.
Anyway, voor het up- en downloaden heb ik nu twee subs geschreven die prima werken. Het idee is heel simpel: in plaats van alle structuur downloaden (wat TranserDatabase doet), worden nu nog slechts gegevens gedownload danwel geupload.
Overigens is deze code natuurlijk prima te gebruiken voor iedereen die gegevens moet uitwisselen met een MySQL-server...
code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
| Public Sub tabel_downloaden_sql(TabelNaam As String)
'tabelnaam die gedownload moet worden meegeven
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sql As String
'eerst de variabelen voor de verbinding invullen
mysql_server = "*********"
mysql_username = "**********"
mysql_password = "**********"
mysql_database = "*****************"
connectstring = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=" & mysql_server & ";" _
& "DATABASE=" & mysql_database & ";" _
& "UID=" & mysql_username & _
";PWD=" & mysql_password & _
";OPTION=1312771"
'code komt deels van Internet
'zie http://www.sourcekeg.co.uk/www.mysql.com/doc/mysql/en/ado--rs-addnew.html
'connect to MySQL server using MySQL ODBC 3.51 Driver
Set conn = New ADODB.Connection
conn.ConnectionString = connectstring
conn.Open
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
rs.Open "SELECT * FROM `" & TabelNaam & "`", conn
rs.MoveFirst
Dim VeldNamenSQL As String
Dim VeldWaardesSQL As String
Do Until rs.EOF
'deze loop wordt herhaald tot de laatste record in de specifieke tabel
'het leidt ertoe dat het volgende commando ontstaat:
'INSERT INTO tabelnaam ([veldnaam1],[veldnaam2],...) VALUES ('veldwaarde1','veldwaarde2'....);
'dat wordt vervolgens uitgevoerd op de lokale database voor dezelfde tabelnaam
'het zaakje openen met haakjes
VeldNamenSQL = "("
VeldWaardesSQL = "("
x = 0
For Each fld In rs.Fields
'in deze loop worden alle VeldNamenSQL en VeldWaardesSQL van een record behandeld
x = x + 1
VeldNamenSQL = VeldNamenSQL & "[" & fld.Name & "]"
VeldWaardesSQL = VeldWaardesSQL & "'" & fld.Value & "'"
If x <> rs.Fields.Count Then
'uitgezonderd bij het laatste veld moet telkens een komma worden toegevoegd
VeldNamenSQL = VeldNamenSQL & ","
VeldWaardesSQL = VeldWaardesSQL & ","
End If
Next
'en vervolgens op het einde het zaakje afsluiten met haakjes
VeldNamenSQL = VeldNamenSQL & ")"
VeldWaardesSQL = VeldWaardesSQL & ")"
With CurrentDb
.Execute "INSERT INTO `" & TabelNaam & "` " & VeldNamenSQL & " VALUES " & VeldWaardesSQL & ";"
End With
DoEvents
rs.MoveNext
Loop
End Sub |
Wat slordig commentaar erbij en wellicht kan het nog wel wat sneller, maar goed, het werkt alvast. Deze sub veronderstelt dus dat de lokale tabel leeg is.
En de sub voor het uploaden....
code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
| Public Sub tabel_uploaden_sql(TabelNaam As String)
'te uploaden tabel meegeven
Dim conn As ADODB.Connection
Dim RsOnline As ADODB.Recordset
Dim fld As ADODB.Field
Dim sql As String
'eerst de variabelen voor de verbinding invullen
mysql_server = "*******"
mysql_username = "**********"
mysql_password = "************"
mysql_database = "************"
connectstring = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=" & mysql_server & ";" _
& "DATABASE=" & mysql_database & ";" _
& "UID=" & mysql_username & _
";PWD=" & mysql_password & _
";OPTION=1312771"
'code komt deels van Internet
'zie http://www.sourcekeg.co.uk/www.mysql.com/doc/mysql/en/ado--rs-addnew.html
'connect to MySQL server using MySQL ODBC 3.51 Driver
Set conn = New ADODB.Connection
conn.ConnectionString = connectstring
conn.Open
Set RsOnline = New ADODB.Recordset
RsOnline.CursorLocation = adUseServer
Dim VeldNamenSQL As String
Dim VeldWaardesSQL As String
Dim VeldWaardes As String
Dim RsLokaal As ADODB.Recordset
Set RsLokaal = New ADODB.Recordset
RsLokaal.Open "SELECT * FROM `" & TabelNaam & "`", CurrentProject.Connection
Do Until RsLokaal.EOF
'deze loop wordt herhaald tot de laatste record in de specifieke tabel
'het leidt ertoe dat het volgende commando ontstaat:
'INSERT INTO tabelnaam ([veldnaam1],[veldnaam2],...) VALUES ('veldwaarde1','veldwaarde2'....);
'dat wordt vervolgens uitgevoerd op de server voor dezelfde tabelnaam
'het zaakje openen met haakjes
VeldNamenSQL = "("
VeldWaardesSQL = "("
x = 0
For Each fld In RsLokaal.Fields
'in deze loop worden alle VeldNamenSQL en VeldWaardesSQL van een record behandeld
x = x + 1
If fld.Type = 7 Then
'in dit geval betreft het een datumtype, dat moet worden omgezet
VeldWaardes = (Format(fld.Value, "yyyy-mm-dd Hh:Nn:Ss"))
Else
'mocht het nu Null hebben als waarde, dan gaat Access zonder deze toevoeging mekkeren
VeldWaardes = "" & fld.Value
End If
VeldNamenSQL = VeldNamenSQL & "`" & fld.Name & "`"
VeldWaardesSQL = VeldWaardesSQL & "'" & VeldWaardes & "'"
If x <> RsLokaal.Fields.Count Then
'uitgezonderd bij het laatste veld moet telkens een komma worden toegevoegd
VeldNamenSQL = VeldNamenSQL & ","
VeldWaardesSQL = VeldWaardesSQL & ","
End If
Next
'en vervolgens op het einde het zaakje afsluiten met haakjes
VeldNamenSQL = VeldNamenSQL & ")"
VeldWaardesSQL = VeldWaardesSQL & ")"
conn.Execute "INSERT INTO `" & TabelNaam & "` " & VeldNamenSQL & " VALUES " & VeldWaardesSQL & ";"
RsLokaal.MoveNext
Loop
DoEvents
End Sub |
Tot zover het algemene deel. Zoals al wel te zien is vereisen beide subs een lege doeltabel. Dus hetzij lokaal, hetzij op de server moet eerst de tabel worden leeggemaakt. En een backup is natuurlijk ook altijd wel fijn, je weet immers nooit. Vandaar onderstaande code.
Waar ik nu tegenaan loop is het volgende: het uploaden is veel trager dan het downloaden.
Niet het uploaden zelf lijkt de oorzaak, maar het maken van een online backup. Het maken van die backup is op zichzelf een handeling die nog geen seconden hoeft te kosten (via bijv. PhpMyAdmin), via Access duurt het opeens veel langer. Uploaden volgens onderstaande code duurt dan ook wel 3x zo lang als downloaden.
Ziet iemand waar het misgaat?
code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
| Public Sub gegevens_uploaden(tabellen As Collection)
Dim conn As ADODB.Connection
Dim RsOnline As ADODB.Recordset
Dim fld As ADODB.Field
Dim sql As String
'eerst de variabelen voor de verbinding invullen
mysql_server = "*****"
mysql_username = "*********"
mysql_password = "*******"
mysql_database = "***********"
connectstring = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=" & mysql_server & ";" _
& "DATABASE=" & mysql_database & ";" _
& "UID=" & mysql_username & _
";PWD=" & mysql_password & _
";OPTION=1312771"
Set conn = New ADODB.Connection
conn.ConnectionString = connectstring
conn.Open
For x = 1 To tabellen.Count
With [Form_uploaden]
.lblStatus.Caption = "Online backup maken van tabel " & x & " van " & tabellen.Count & vbNewLine
.Repaint
End With
'backuptabel eerst leegmaken
sql = "TRUNCATE TABLE `" & tabellen(x) & "_backup`"
conn.Execute (sql)
'dan backup-tabel vullen
sql = "INSERT INTO `" & tabellen(x) & "_backup` SELECT * FROM `" & tabellen(x) & "`;"
conn.Execute (sql)
With [Form_uploaden]
.lblStatus.Caption = "Online leegmaken van doeltabel " & x & " van " & tabellen.Count & vbNewLine & .lblStatus.Caption
.Repaint
End With
'dan oorspronkelijke tabel leegmaken
sql = "TRUNCATE TABLE `" & tabellen(x) & "`"
conn.Execute (sql)
DoEvents
With [Form_uploaden]
.lblStatus.Caption = "Gegevens uploaden van tabel " & x & " van " & tabellen.Count & vbNewLine & .lblStatus.Caption
.Repaint
End With
'gegevens uploaden
Call tabel_uploaden_sql(tabellen(x))
With [Form_uploaden]
.lblStatus.Caption = "Uploaden van tabel " & x & " van " & tabellen.Count & " geslaagd" & vbNewLine & .lblStatus.Caption
.lblGeduld.Caption = Format((x / tabellen.Count), "0.00%")
End With
DoEvents
Next
conn.Close
MsgBox ("Uploaden is klaar en geslaagd!")
End Sub |
Op zich een heel eenvoudige sub. Waarom moet dit gebeuren nu zo enorm veel langer duren dan het downloaden van de gegevens? Daar maak ik immers ook een lokale backup.
Met name deze regels kunnen online in hooguit twee seconden gebeurd zijn:
code:
1
2
3
4
5
6
| 'backuptabel eerst leegmaken
sql = "TRUNCATE TABLE `" & tabellen(x) & "_backup`"
conn.Execute (sql)
'dan backup-tabel vullen
sql = "INSERT INTO `" & tabellen(x) & "_backup` SELECT * FROM `" & tabellen(x) & "`;"
conn.Execute (sql) |
Wat moet ik omgooien, waar gaat het mis?
