windows - Using Excel as an ODBC database -

i'd know, how create database table in excel, may used odbc

i want use odbc, , have 2 options, either ms access or excel,

as know, in order indicate ms access file or excel file odbc source, need follow:

administrative tools -> data sources (odbc) -> choose user dsn -> choose either 'excel files' or 'ms access database' list -> press 'configure' -> choose file (ms access or excel) odbc source

well, works fine ms access, can connect file , see tables i've created inside

but when comes excel, although can connect file, can't see table i've created inside

i used 'table' in 'insert' tab, added headers column names, , gave table meaningful name. way it?

there several ways can reference "table" data in excel workbook:

• an entire worksheet.
• a named range of cells on worksheet.
• an unnamed range of cells on worksheet.

they explained in detail in "select excel data code" section of microsoft knowledge base article 257819.

the straightforward way keep data on separate sheet, put column names in first row (starting in cell a1), , have actual data start in row 2, this

to test, created user dsn named "odbcfromexcel" pointed workbook...

...and ran following vbscript test connection:

option explicit dim con, rst, rowcount set con = createobject("adodb.connection") con.open "dsn=odbcfromexcel;" set rst = createobject("adodb.recordset") rst.open "select * [sheet1$]", con rowcount = 0 while not rst.eof     rowcount = rowcount + 1     if rowcount = 1         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 

the results were

c:\users\gord\documents\__tmp>cscript /nologo exceltest.vbs data row 1, rst("lastname").value="thompson" 10 data rows found. 

i hope helps excel connection issue.

as final comment have if doing takes "several seconds" in excel "takes around 20-25 min" in access suspect using access in very inefficient way, that's topic question (if care pursue it).

edit

if want insert data excel workbook possible, aware default setting excel odbc connection "read only" have click "options>>" button , clear checkbox:

once that's done, following code...

option explicit dim con set con = createobject("adodb.connection") con.open "dsn=odbcfromexcel;" con.execute "insert [sheet1$] (id, lastname, firstname) values (11, 'dumpty', 'humpty')" con.close set con = nothing wscript.echo "done." 

...will indeed append new row in excel sheet data provided.

however, still doesn't address problem of no "tables" being available selection when point "sniffer" app @ excel odbc dsn.

one thing try create excel sheet column headings in row 1, select entire columns , create excel "defined name". then, see if "sniffer" app recognizes "table" name can select.

fwiw, defined name mytable =sheet1!$a:$c in excel workbook, , original code sort of worked when used select * [mytable]:

c:\users\gord\documents\__tmp>cscript /nologo exceltest.vbs data row 1, rst("lastname").value="thompson" 1048576 data rows found. 

as can see, retrieved first "record" correctly, didn't recognize end of valid data , continued read ~1 million rows in sheet.

i doubt putting more effort because agree other comments using excel "odbc database" not idea.

i suggest try find out why earlier attempts use access unsatisfactory. said before, sounds me doing really bad job @ interacting access.