Pertanyaan Menggunakan Excel sebagai database ODBC


Saya ingin tahu, bagaimana membuat tabel database di Excel, sehingga dapat digunakan dengan ODBC

Saya ingin menggunakan ODBC, dan saya memiliki dua opsi, baik MS Access atau Excel,

Seperti yang mungkin Anda ketahui, untuk menunjukkan beberapa file MS Access atau file Excel sebagai sumber ODBC, Anda harus mengikuti:

Alat Administratif -> Sumber Data (ODBC) -> Pilih Pengguna DSN -> Pilih 'File Excel' atau 'MS Access Database' dari daftar -> Tekan 'Konfigurasikan' -> akhirnya pilih file (MS Access atau Excel) sebagai Sumber ODBC

Yah, ini berfungsi dengan baik dengan MS Access, saya dapat terhubung ke file dan melihat semua tabel yang saya buat di dalamnya

Tetapi ketika datang ke Excel, meskipun saya dapat terhubung ke file, saya tidak bisa melihat tabel yang saya buat di dalamnya

Saya hanya menggunakan 'Tabel' di tab 'Sisipkan', menambahkan beberapa header sebagai nama kolom, dan memberi tabel nama yang berarti. Apakah itu cara melakukannya?


8
2018-04-05 22:25


asal


Jawaban:


Ada beberapa cara Anda bisa mereferensikan data "tabel" di buku kerja Excel:

  • Seluruh lembar kerja.
  • Berbagai sel bernama pada lembar kerja.
  • Kisaran sel yang tidak disebutkan namanya di lembar kerja.

Mereka dijelaskan secara rinci di bagian "Pilih Data Excel dengan Kode" dari artikel Pangkalan Pengetahuan Microsoft 257819.

Cara yang paling mudah adalah menyimpan data pada lembar terpisah, memasukkan nama kolom di baris pertama (mulai di sel A1), dan kemudian memiliki data aktual mulai di baris 2, seperti ini

Excel

Untuk menguji, saya membuat Pengguna DSN bernama "odbcFromExcel" yang menunjuk ke buku kerja itu ...

ODBC

... dan kemudian jalankan VBScript berikut untuk menguji koneksi:

Option Explicit
Dim con, rst, rowCount
Set con = CreateObject("ADODB.Connection")
con.Open "DSN=odbcFromExcel;"
Set rst = CreateObject("ADODB.Recordset")
rst.Open "SELECT * FROM [Sheet1$]", con
rowCount = 0
Do While Not rst.EOF
    rowCount = rowCount + 1
    If rowCount = 1 Then
        Wscript.Echo "Data row 1, rst(""LastName"").Value=""" &  rst("LastName").Value & """"
    End If
    rst.MoveNext
Loop
Wscript.Echo rowCount & " data rows found."
rst.Close
Set rst = Nothing
con.Close
Set con = Nothing

Hasilnya adalah

C:\Users\Gord\Documents\__tmp>cscript /nologo excelTest.vbs
Data row 1, rst("LastName").Value="Thompson"
10 data rows found.

Saya harap itu membantu masalah koneksi Excel Anda.

Sebagai komentar terakhir saya harus mengatakan bahwa jika Anda melakukan sesuatu yang membutuhkan "beberapa detik" untuk dilakukan di Excel, tetapi "membutuhkan waktu sekitar 20-25 menit" untuk dilakukan di Access maka saya sangat curiga bahwa Anda menggunakan Access dalam sangat cara yang tidak efisien, tapi itu topik untuk pertanyaan lain (jika Anda peduli untuk mengejarnya).

EDIT

Jika Anda ingin INSERT data ke dalam buku kerja Excel maka itu mungkin, tetapi perlu diketahui bahwa pengaturan default untuk koneksi ODBC Excel adalah "Hanya Baca" sehingga Anda harus mengklik "Opsi >>" tombol dan menghapus kotak centang:

readonly

Setelah itu selesai, kode berikut ...

Option Explicit
Dim con
Set con = CreateObject("ADODB.Connection")
con.Open "DSN=odbcFromExcel;"
con.Execute "INSERT INTO [Sheet1$] (ID, LastName, FirstName) VALUES (11, 'Dumpty', 'Humpty')"
con.Close
Set con = Nothing
Wscript.Echo "Done."

... memang akan menambahkan baris baru di lembar Excel dengan data yang disediakan.

Namun, masih belum mengatasi masalah "Tables" yang tersedia untuk dipilih ketika Anda mengarahkan aplikasi "sniffer" Anda ke Excel ODBC DSN.

Satu hal yang dapat Anda coba adalah membuat lembar Excel dengan judul kolom di baris 1, lalu pilih itu seluruh kolom dan buat Excel "Defined Name". Kemudian, lihat apakah aplikasi "sniffer" Anda mengenali itu sebagai nama "tabel" yang dapat Anda pilih.

FWIW, saya mendefinisikan namanya myTable sebagai =Sheet1!$A:$C di buku kerja Excel saya, dan kemudian kode asli saya semacam bekerja ketika saya menggunakannya SELECT * FROM [myTable]:

C:\Users\Gord\Documents\__tmp>cscript /nologo excelTest.vbs
Data row 1, rst("LastName").Value="Thompson"
1048576 data rows found.

Seperti yang Anda lihat, itu mengambil "catatan" pertama dengan benar, tetapi kemudian tidak mengenali akhir dari data yang valid dan terus membaca ~ 1 juta baris dalam lembaran.

Saya sangat meragukan bahwa saya akan menempatkan upaya lebih dalam ini karena saya setuju dengan komentar lain yang menggunakan Excel sebagai "database ODBC" benar-benar bukan ide yang sangat bagus.

Saya sangat menyarankan Anda mencoba mencari tahu mengapa upaya Anda sebelumnya untuk menggunakan Access sangat tidak memuaskan. Seperti yang saya katakan sebelumnya, kedengarannya seperti ada sesuatu yang dilakukan pekerjaan yang sangat buruk saat berinteraksi dengan Access.


13
2018-04-06 10:44



Saya mengalami masalah yang sama dengan beberapa data baru-baru ini. Cara saya berhasil menyiasati itu untuk memilih data sebagai kisaran A1: XY12345, kemudian gunakan alat Define Name untuk menamai kisaran. Ketika Anda terhubung ke buku kerja Excel melalui ODBC, rentang bernama ini akan muncul sebagai "tabel," sementara rentang yang sebenarnya Anda tentukan (per Excel) sebagai tabel, tidak.


1
2018-03-28 14:50



Anda hanya perlu memilih sebanyak kolom yang diperlukan dari baris pertama file excel Anda dan kemudian beri nama untuk itu pada kotak edit yang tersisa ke bilah rumus. Tentu saja Anda memberi nama untuk setiap kolom file juga!


0
2018-01-30 14:15