ACCESS FEED: RE: [belajar-access] Query Union Error ketika tabel terkoneksi ke MySql

Errornya kemungkinan karena ada reference yang belum di masukkan (dicentang). Coba design form > Code > Tools > References.
Beri Centang ini:
- Visual Basic For Applications
- Microfost Access 11.0 Object Library
- OLE Automation
- Microsoft DAO 3.6 Objcet Library
- Microsoft ActiveX Data Object 2.1 Library

Penggantian script koneksi

Function KONEKSI()
'txtServer ==> Nama server/IP
', txtUID, ==> User Mysql (pastikan user ini jangan yang super admin, tapi memiliki hak insert, delete, update, select)
'txtPWD, ===> Password untuk user Mysql diatas. Misal kita bikin user root password 123 haknya grant (semua, super admin)
'Ditambah lagi user Gugus Password 432, hak akses insert, delete, update, select. Bisa digunakan yang Gugus dengan password 432
'3306, ==>Port (standard Mysql 3306, ada juga yang memakai Port lain. Misal 13007,13004 dll
'txtDb, ==> Nama database, jangan lupa user Gugus diberi hak juga mengakses database yang dipilih.

connToDB "10.12.0.2", "root", "admin", 3306,"Production"

End Function

Silahkan dicoba. Bila ada error, komunikasikan...

Semoga bisa membantu dan memberi semangat.

Hariyanto (Surabaya)

--------------------------------------------
On Tue, 2/8/16, 'Cecep' cepwahyu@gmail.com [belajar-access] <belajar-access@yahoogroups.com> wrote:

Subject: RE: [belajar-access] Query Union Error ketika tabel terkoneksi ke MySql
To: belajar-access@yahoogroups.com
Date: Tuesday, 2 August, 2016, 9:30 AM






   Mas Hariyanto, cara yang mas kasih
sudah sy copa terapkan tapi saya menemui error
sbb: Sebagai informasi tambahan BE-nya
ada LAN dengan IP 10.12.0.2, apakah betul jika baris berikut
sy ganti sbb:Function KONEKSI()
'connToDB txtServer, txtUID, txtPWD, 3306,
txtDb
connToDB "localhost",
"root", "admin", 3306,
"Nama_database"
End Function

Menjadi :Function KONEKSI()
'connToDB txtServer, txtUID, txtPWD, 3306,
txtDb
connToDB "10.12.0.2",
"root", "admin", 3306,
"Production"
End Function Best RegardsCW From:

belajar-access@yahoogroups.com
[mailto:belajar-access@yahoogroups.com]
Sent: 01 Agustus 2016 12:02
To: belajar-access@yahoogroups.com
Subject: RE: [belajar-access] Query
Union Error ketika tabel terkoneksi ke
MySql    Begini...,

1. Buat modul koneksi dan nama komputer, karena
akan di pakai di hampir semua form. Nama komputer digunakan
untuk nama tabel biar unik antar masing-masing user

Ini scriptnya:

Option Explicit

Public conn As New ADODB.Connection
'deklarasi koneksi

Private Const MAX_COMPUTERNAME As Long = 15
Private Declare Function GetComputerName Lib
"kernel32" _
Alias
"GetComputerNameA" _
(ByVal
lpBuffer As String, _
nSize As Long) As
Long

Private Function
TrimNull(item As String)

'return string before the terminating
null
Dim pos As Integer

pos = InStr(item, Chr$(0))

If pos Then
TrimNull =
Left$(item, pos - 1)
Else
TrimNull = item
End If

End Function

Function KOM()
'untuk membuat tabel berdasarkan nama
komputer

Dim tas As
String

'pre-load the
text boxes with
'the local computer name
for testing
tas = Space$(MAX_COMPUTERNAME +
1)
Call GetComputerName(tas, Len(tas))

KOM = TrimNull(tas)
If KOM Like "*-*" Then
KOM = Replace(KOM, "-",
"_")
End If
End
Function

Public Function
connToDB(ServerName As String, _
UserName As
String, userPass As Variant, _
dbPath As
String, dbName As String)

Dim strCon As String

On Error GoTo errHandle
'sesuaikan driver mysqlnya, ada yang pakai
3.51
strCon = "DRIVER={MySQL ODBC 5.1
Driver};SERVER=" _
& ServerName
& ";DATABASE=" & dbName &
";" & _
"UID=" &
UserName & ";PWD=" & userPass &
";OPTION=16426"

Set conn = New ADODB.Connection
conn.Open strCon

Exit Function

errHandle:
MsgBox "SERVER
SEDANG TIDAK AKTIF", , "NON AKTIF"
conn.Close
Set conn =
Nothing
End Function

Public Function EscapeQuotes(s) As String

If s = "" Then
EscapeQuotes = ""
ElseIf Left(s, 1) = "'" Then
EscapeQuotes = "''" &
EscapeQuotes(Mid(s, 2))
Else

EscapeQuotes = Left(s, 1)
& EscapeQuotes(Mid(s, 2))
End If

End Function

Function hp_tb(n_tb)
Dim rbs As Recordset
Dim db As
DAO.Database
'untuk menghapus tabel
Access temporer
Set rbs =
CurrentDb.OpenRecordset("SELECT MSysObjects.Name"
_
& " FROM MSysObjects WHERE
MSysObjects.Type= 1 And MSysObjects.Flags=0" _
& " and MSysObjects.Name='"
& n_tb & "'") 'melihat nama tabel
n_tb
If Not rbs.EOF Then 'bila ada
record
Set db = CurrentDb 'menetapkan
bahwa db adalah database ini
db.TableDefs.Delete n_tb 'hapus tabel
'menghilangkan dari memory komputer
db.Close
Set db = Nothing
End If
'menghilangkan dari
memory komputer
rbs.Close
Set rbs = Nothing

End Function

Function KONEKSI()
'connToDB txtServer, txtUID, txtPWD, 3306,
txtDb
connToDB "localhost",
"root", "admin", 3306,
"Nama_database"
End Function

2. Kita mulai bermain di form.
Misal nama comboBoxnya contoh.Propertinya:
Control Source ==> kosongkan
Row Source Type ==> Table/query
Row Source ==> kosongkan
Coloum Count ==> 1
Coloum
Head ==> No

3. Misal,
event yang kita manfaatkan adalah saat, pertama kali
loading.

Private Sub
Form_Load()
Dim tb as Variant
Dim db As Database
Dim rsp As
ADODB.Recordset 'deklarasi rsp sebagai ADODB
recordset

KONEKSI
'melakukan koneksi
If conn.State
<> 0 Then 'bila sudah konek

contoh.RowSource = ""
'menghilangkan recordsource dulu biar tidak error
contoh.Visible = False 'tidak ditampilkan
dulu
tb = "temp_" & KOM
'nama tabel temporer yang akan kita buat
hp_tb (tb) 'hapus tabel temporer dulu bila
sudah ada
'buat tabel temporer asumsi
nama field=field1
'size 255
DoCmd.RunSQL "CREATE TABLE " _
& tb & " (field1
Text(255));"
'melihat data di tabel
mysql
Set rsp = conn.Execute("SELECT
left([Week_Subc],5)" _
& "
From TblCultureProduction UNION" _
& " SELECT left([Week_Subc],5)
From" _
& "
TblCultureIncoming")
If Not rsp.EOF
Then
DoCmd.Hourglass True
Set db = CurrentDb 'deklarasi db adalah
currentdb
'mengisi record dengan
--All--
db.Execute ("Insert into "
_
& tb & " Values
('--All---')")
Do While Not
rsp.EOF 'jika ada data, lakukan berulang sampai akhir
If rsp.Fields(0) <> "" Then
'jika record berisi
'isi tabel
temporer dengan data Mysql
db.Execute
("Insert into " _
& tb &
" Values ('" _
&
EscapeQuotes(rsp.Fields(0)) _
&
"')")
End If
rsp.MoveNext
Loop

db.Close
Set db = Nothing
DoCmd.Hourglass False
End If
rsp.Close
Set rsp = Nothing
contoh.RowSource = tb 'letakkan
recordsource combo contoh
contoh.DefaultValue =
"""--All--""" 'menjadikan
--All-- sebagai devault value
contoh.Visible
= Trye 'Tampilkan
contoh.Requery
'refresh
Else
MsgBox
"gagal koneksi"
End If
conn.Close 'menutup koneksi
Set conn = Nothing 'menghilangkan koneksi
dari memori

End Sub

Semoga bisa membantu dan
memberi semangat.

Hariyanto
(Surabaya)






#yiv6925663526 #yiv6925663526 --
   #yiv6925663526ygrp-mkp {
border:1px solid #d8d8d8;font-family:Arial;margin:10px
0;padding:0 10px;}

#yiv6925663526 #yiv6925663526ygrp-mkp hr {
border:1px solid #d8d8d8;}

#yiv6925663526 #yiv6925663526ygrp-mkp #yiv6925663526hd {
color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}

#yiv6925663526 #yiv6925663526ygrp-mkp #yiv6925663526ads {
margin-bottom:10px;}

#yiv6925663526 #yiv6925663526ygrp-mkp .yiv6925663526ad {
padding:0 0;}

#yiv6925663526 #yiv6925663526ygrp-mkp .yiv6925663526ad p {
margin:0;}

#yiv6925663526 #yiv6925663526ygrp-mkp .yiv6925663526ad a {
color:#0000ff;text-decoration:none;}
#yiv6925663526 #yiv6925663526ygrp-sponsor
#yiv6925663526ygrp-lc {
font-family:Arial;}

#yiv6925663526 #yiv6925663526ygrp-sponsor
#yiv6925663526ygrp-lc #yiv6925663526hd {
margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}

#yiv6925663526 #yiv6925663526ygrp-sponsor
#yiv6925663526ygrp-lc .yiv6925663526ad {
margin-bottom:10px;padding:0 0;}

#yiv6925663526 #yiv6925663526actions {
font-family:Verdana;font-size:11px;padding:10px 0;}

#yiv6925663526 #yiv6925663526activity {
background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}

#yiv6925663526 #yiv6925663526activity span {
font-weight:700;}

#yiv6925663526 #yiv6925663526activity span:first-child {
text-transform:uppercase;}

#yiv6925663526 #yiv6925663526activity span a {
color:#5085b6;text-decoration:none;}

#yiv6925663526 #yiv6925663526activity span span {
color:#ff7900;}

#yiv6925663526 #yiv6925663526activity span
.yiv6925663526underline {
text-decoration:underline;}

#yiv6925663526 .yiv6925663526attach {
clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}

#yiv6925663526 .yiv6925663526attach div a {
text-decoration:none;}

#yiv6925663526 .yiv6925663526attach img {
border:none;padding-right:5px;}

#yiv6925663526 .yiv6925663526attach label {
display:block;margin-bottom:5px;}

#yiv6925663526 .yiv6925663526attach label a {
text-decoration:none;}

#yiv6925663526 blockquote {
margin:0 0 0 4px;}

#yiv6925663526 .yiv6925663526bold {
font-family:Arial;font-size:13px;font-weight:700;}

#yiv6925663526 .yiv6925663526bold a {
text-decoration:none;}

#yiv6925663526 dd.yiv6925663526last p a {
font-family:Verdana;font-weight:700;}

#yiv6925663526 dd.yiv6925663526last p span {
margin-right:10px;font-family:Verdana;font-weight:700;}

#yiv6925663526 dd.yiv6925663526last p
span.yiv6925663526yshortcuts {
margin-right:0;}

#yiv6925663526 div.yiv6925663526attach-table div div a {
text-decoration:none;}

#yiv6925663526 div.yiv6925663526attach-table {
width:400px;}

#yiv6925663526 div.yiv6925663526file-title a, #yiv6925663526
div.yiv6925663526file-title a:active, #yiv6925663526
div.yiv6925663526file-title a:hover, #yiv6925663526
div.yiv6925663526file-title a:visited {
text-decoration:none;}

#yiv6925663526 div.yiv6925663526photo-title a,
#yiv6925663526 div.yiv6925663526photo-title a:active,
#yiv6925663526 div.yiv6925663526photo-title a:hover,
#yiv6925663526 div.yiv6925663526photo-title a:visited {
text-decoration:none;}

#yiv6925663526 div#yiv6925663526ygrp-mlmsg
#yiv6925663526ygrp-msg p a span.yiv6925663526yshortcuts {
font-family:Verdana;font-size:10px;font-weight:normal;}

#yiv6925663526 .yiv6925663526green {
color:#628c2a;}

#yiv6925663526 .yiv6925663526MsoNormal {
margin:0 0 0 0;}

#yiv6925663526 o {
font-size:0;}

#yiv6925663526 #yiv6925663526photos div {
float:left;width:72px;}

#yiv6925663526 #yiv6925663526photos div div {
border:1px solid
#666666;min-height:62px;overflow:hidden;width:62px;}

#yiv6925663526 #yiv6925663526photos div label {
color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}

#yiv6925663526 #yiv6925663526reco-category {
font-size:77%;}

#yiv6925663526 #yiv6925663526reco-desc {
font-size:77%;}

#yiv6925663526 .yiv6925663526replbq {
margin:4px;}

#yiv6925663526 #yiv6925663526ygrp-actbar div a:first-child {
margin-right:2px;padding-right:5px;}

#yiv6925663526 #yiv6925663526ygrp-mlmsg {
font-size:13px;font-family:Arial, helvetica, clean,
sans-serif;}

#yiv6925663526 #yiv6925663526ygrp-mlmsg table {
font-size:inherit;font:100%;}

#yiv6925663526 #yiv6925663526ygrp-mlmsg select,
#yiv6925663526 input, #yiv6925663526 textarea {
font:99% Arial, Helvetica, clean, sans-serif;}

#yiv6925663526 #yiv6925663526ygrp-mlmsg pre, #yiv6925663526
code {
font:115% monospace;}

#yiv6925663526 #yiv6925663526ygrp-mlmsg * {
line-height:1.22em;}

#yiv6925663526 #yiv6925663526ygrp-mlmsg #yiv6925663526logo {
padding-bottom:10px;}

#yiv6925663526 #yiv6925663526ygrp-msg p a {
font-family:Verdana;}

#yiv6925663526 #yiv6925663526ygrp-msg
p#yiv6925663526attach-count span {
color:#1E66AE;font-weight:700;}

#yiv6925663526 #yiv6925663526ygrp-reco
#yiv6925663526reco-head {
color:#ff7900;font-weight:700;}

#yiv6925663526 #yiv6925663526ygrp-reco {
margin-bottom:20px;padding:0px;}

#yiv6925663526 #yiv6925663526ygrp-sponsor #yiv6925663526ov
li a {
font-size:130%;text-decoration:none;}

#yiv6925663526 #yiv6925663526ygrp-sponsor #yiv6925663526ov
li {
font-size:77%;list-style-type:square;padding:6px 0;}

#yiv6925663526 #yiv6925663526ygrp-sponsor #yiv6925663526ov
ul {
margin:0;padding:0 0 0 8px;}

#yiv6925663526 #yiv6925663526ygrp-text {
font-family:Georgia;}

#yiv6925663526 #yiv6925663526ygrp-text p {
margin:0 0 1em 0;}

#yiv6925663526 #yiv6925663526ygrp-text tt {
font-size:120%;}

#yiv6925663526 #yiv6925663526ygrp-vital ul li:last-child {
border-right:none !important;
}
#yiv6925663526