Rabu, 11 April 2012

Re: [belajar-access] Import Record exel ke table access [1 Attachment]

 
[Attachment(s) from Trisno included below]

mas Andi sudah aku coba-coba tp ko ga bisa, ada peringatan compile Error. dimananya yang error ya mas.

mohon pencerahan, file terlampir

Terima kasih


From: Andi Ali <aliandi05@yahoo.com>
To: "belajar-access@yahoogroups.com" <belajar-access@yahoogroups.com>
Sent: Wednesday, April 11, 2012 11:59 PM
Subject: Re: [belajar-access] Import Record exel ke table access

 
Mas Trisno (maaf kalo nama panggilannya salah ya")
mungkin ini bisa membantu,

copy code berikut:

Function CopyRecordDariExcel()
 
'Tahap 1: Deklarasi nama object dan variable yg digunakan
    Dim MyConnect As String
    Dim myrecordset As New ADODB.Recordset
    Dim MyTable As ADODB.Recordset
    Dim MySQL As String
    Dim serverName As String
    Dim UserName As String
    Dim userPass As String
    Dim dbPath As String
    Dim dbName As String
           
'Tahap 2: Deklarasi jenis koneksi database dan lokasinya
    MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & CurrentProject.Path & "\nama file di excel.xlsm;" & _
                "Extended Properties=Excel 12.0"
 
'Tahap 3:  Membuat kode SQL
    MySQL = " SELECT * FROM [sheet1 di excel$]"
 
'Tahap 4:  Inisialisasi recordset
    Set myrecordset = New ADODB.Recordset
    myrecordset.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly
 
'Tahap 5:  Membuka table di Access menjadi Recordset
    Set MyTable = New ADODB.Recordset
    MyTable.Open "nama tabel di access", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
 
'Tahap 6:  Import data dari excel ke table di Access
    Do Until myrecordset.EOF
    MyTable.AddNew

MyTable![nama kolom1 di excel] = myrecordset![nama field 1 di tabel access]
MyTable![nama kolom2 di excel] = myrecordset![nama field 2 di tabel access] 
MyTable![nama kolom3 di excel] = myrecordset![nama field 3 di tabel access]    

MyTable.Update
    myrecordset.MoveNext
    Loop
 
'Tahap 7: Bersihkan memory di VBA
    myrecordset.close
    MyTable.close
    Set myrecordset = Nothing
    Set MyTable = Nothing
    
End Function

kemudian buat file Excel yang akan di import ke access, kemudian simpan pada folder yang sama dengan file access...
buat suatu commmand button kemudian masukkan pada on klik kode berikut:

Private Sub Command0_Click()
Call CopyRecordDariExcel
MsgBox "Import data dari Excel ke tabel berhasil....", vbOKOnly, "Informasi"
DoCmd.close 
End Sub

semoga membantu
Andi

From: Soetrisno <trisno_ae@yahoo.com>
To: belajar-access@yahoogroups.com
Sent: Tuesday, April 10, 2012 3:47 PM
Subject: [belajar-access] Import Record exel ke table access

 
dear master2 access

mohon pencerahan bagaimana membuat fungsi import record file exel ke table access.

terima kasih





__._,_.___

Attachment(s) from Trisno

1 of 1 File(s)

Recent Activity:
SPAM IS PROHIBITED
.

__,_._,___

Tidak ada komentar:

Posting Komentar