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

Hapus fungsi koneksi di module. Lalu, pindahkan ke Form.

--------------------------------------------
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, 2:17 PM

Sekarang error-nya di koneksi
mas.Error-nya sbb: Kira2 masalahnya apa
mas? Best regardsCW From:
belajar-access@yahoogroups.com
[mailto:belajar-access@yahoogroups.com]
Sent: 02 Agustus 2016 11:39
To: belajar-access@yahoogroups.com
Subject: 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

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

#yiv0700038316 #yiv0700038316ygrp-mkp hr {
border:1px solid #d8d8d8;}

#yiv0700038316 #yiv0700038316ygrp-mkp #yiv0700038316hd {
color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}

#yiv0700038316 #yiv0700038316ygrp-mkp #yiv0700038316ads {
margin-bottom:10px;}

#yiv0700038316 #yiv0700038316ygrp-mkp .yiv0700038316ad {
padding:0 0;}

#yiv0700038316 #yiv0700038316ygrp-mkp .yiv0700038316ad p {
margin:0;}

#yiv0700038316 #yiv0700038316ygrp-mkp .yiv0700038316ad a {
color:#0000ff;text-decoration:none;}
#yiv0700038316 #yiv0700038316ygrp-sponsor
#yiv0700038316ygrp-lc {
font-family:Arial;}

#yiv0700038316 #yiv0700038316ygrp-sponsor
#yiv0700038316ygrp-lc #yiv0700038316hd {
margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}

#yiv0700038316 #yiv0700038316ygrp-sponsor
#yiv0700038316ygrp-lc .yiv0700038316ad {
margin-bottom:10px;padding:0 0;}

#yiv0700038316 #yiv0700038316actions {
font-family:Verdana;font-size:11px;padding:10px 0;}

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

#yiv0700038316 #yiv0700038316activity span {
font-weight:700;}

#yiv0700038316 #yiv0700038316activity span:first-child {
text-transform:uppercase;}

#yiv0700038316 #yiv0700038316activity span a {
color:#5085b6;text-decoration:none;}

#yiv0700038316 #yiv0700038316activity span span {
color:#ff7900;}

#yiv0700038316 #yiv0700038316activity span
.yiv0700038316underline {
text-decoration:underline;}

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

#yiv0700038316 .yiv0700038316attach div a {
text-decoration:none;}

#yiv0700038316 .yiv0700038316attach img {
border:none;padding-right:5px;}

#yiv0700038316 .yiv0700038316attach label {
display:block;margin-bottom:5px;}

#yiv0700038316 .yiv0700038316attach label a {
text-decoration:none;}

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

#yiv0700038316 .yiv0700038316bold {
font-family:Arial;font-size:13px;font-weight:700;}

#yiv0700038316 .yiv0700038316bold a {
text-decoration:none;}

#yiv0700038316 dd.yiv0700038316last p a {
font-family:Verdana;font-weight:700;}

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

#yiv0700038316 dd.yiv0700038316last p
span.yiv0700038316yshortcuts {
margin-right:0;}

#yiv0700038316 div.yiv0700038316attach-table div div a {
text-decoration:none;}

#yiv0700038316 div.yiv0700038316attach-table {
width:400px;}

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

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

#yiv0700038316 div#yiv0700038316ygrp-mlmsg
#yiv0700038316ygrp-msg p a span.yiv0700038316yshortcuts {
font-family:Verdana;font-size:10px;font-weight:normal;}

#yiv0700038316 .yiv0700038316green {
color:#628c2a;}

#yiv0700038316 .yiv0700038316MsoNormal {
margin:0 0 0 0;}

#yiv0700038316 o {
font-size:0;}

#yiv0700038316 #yiv0700038316photos div {
float:left;width:72px;}

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

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

#yiv0700038316 #yiv0700038316reco-category {
font-size:77%;}

#yiv0700038316 #yiv0700038316reco-desc {
font-size:77%;}

#yiv0700038316 .yiv0700038316replbq {
margin:4px;}

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

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

#yiv0700038316 #yiv0700038316ygrp-mlmsg table {
font-size:inherit;font:100%;}

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

#yiv0700038316 #yiv0700038316ygrp-mlmsg pre, #yiv0700038316
code {
font:115% monospace;}

#yiv0700038316 #yiv0700038316ygrp-mlmsg * {
line-height:1.22em;}

#yiv0700038316 #yiv0700038316ygrp-mlmsg #yiv0700038316logo {
padding-bottom:10px;}

#yiv0700038316 #yiv0700038316ygrp-msg p a {
font-family:Verdana;}

#yiv0700038316 #yiv0700038316ygrp-msg
p#yiv0700038316attach-count span {
color:#1E66AE;font-weight:700;}

#yiv0700038316 #yiv0700038316ygrp-reco
#yiv0700038316reco-head {
color:#ff7900;font-weight:700;}

#yiv0700038316 #yiv0700038316ygrp-reco {
margin-bottom:20px;padding:0px;}

#yiv0700038316 #yiv0700038316ygrp-sponsor #yiv0700038316ov
li a {
font-size:130%;text-decoration:none;}

#yiv0700038316 #yiv0700038316ygrp-sponsor #yiv0700038316ov
li {
font-size:77%;list-style-type:square;padding:6px 0;}

#yiv0700038316 #yiv0700038316ygrp-sponsor #yiv0700038316ov
ul {
margin:0;padding:0 0 0 8px;}

#yiv0700038316 #yiv0700038316ygrp-text {
font-family:Georgia;}

#yiv0700038316 #yiv0700038316ygrp-text p {
margin:0 0 1em 0;}

#yiv0700038316 #yiv0700038316ygrp-text tt {
font-size:120%;}

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