Unrecognized DB Format-Excel VBA to Access Database -
im using .accdb file , connecting following code, has worked me multiple times, don't know why corrupting file time.
dbpath = activeworkbook.path & "\waitanalysisdb.accdb" tblname = "wait_data_table" strcon = "provider=microsoft.ace.oledb.12.0;data source='" & dbpath & "';" conn.open strcon does "unrecognized format" access error occur due error in connection string, or sql statement inserting records well? thanks
here's code, if cares through it. in loops build sql statemetn (rcddetail variable), have if statement, says if there blank in column a, use row above isnt blank.
dim conn new adodb.connection, rs new adodb.recordset, dbpath string, tblname string dim rngcolheads range, rngtblrcds range, colhead string, rcddetail string dim ch integer, cl integer, notnull boolean, strcon string, lr integer dim currentdate string dim strdbcheck string 'code checks if there records date in db 'if there is, skips sql code currentdate = date dbpath = activeworkbook.path & "\waitanalysisdb.accdb" tblname = "wait_data_table" strcon = "provider=microsoft.ace.oledb.12.0;data source='" & dbpath & "';" conn.open strcon strdbcheck = "select * " & tblname rs.open strdbcheck, conn rs.filter = "date= #" & currentdate & "#" if not rs.eof set rs = nothing set conn = nothing goto skipexport else set rs = nothing set conn = nothing goto export end if export: 'set connections dbpath = activeworkbook.path & "\waitanalysisdb.accdb" tblname = "wait_data_table" 'create date column worksheets("wait analysis data").select lr = cells(rows.count, "k").end(xlup).row currentdate = date: range("o1").value = "date": range(range("o2"), range("o" & lr)).value = currentdate set rngcolheads = activesheet.range(range("a1"), range("a1").end(xltoright)) set rngtblrcds = activesheet.range(range("k2:k" & lr).offset(0, -10), range("k2:k" & lr).offset(0, 4)) 'sql connection string strcon = "provider=microsoft.ace.oledb.12.0;data source='" & dbpath & "';" 'create string columns sql colhead = "([" ch = 1 rngcolheads.count colhead = colhead & rngcolheads.columns(ch).value select case ch case = rngcolheads.count colhead = colhead & "])" case else colhead = colhead & "],[" end select next ch on error goto endupdate conn.open strcon conn.begintrans dim tempcl integer cl = 1 rngtblrcds.rows.count if range("a2").offset(cl - 1, 0) = "" tempcl = cl - range("a2").offset(cl, 0).end(xlup).rows.count notnull = false rcddetail = "('" ch = 1 rngcolheads.count select case rngtblrcds.rows(tempcl).columns(ch).value case = empty select case ch case = rngcolheads.count rcddetail = left(rcddetail, len(rcddetail) - 1) & "null)" case else rcddetail = left(rcddetail, len(rcddetail) - 1) & "null,'" end select case else notnull = true select case ch case "11": rcddetail = rcddetail & rngtblrcds.rows(cl).columns(ch).value & "','" case = rngcolheads.count rcddetail = rcddetail & rngtblrcds.rows(tempcl).columns(ch).value & "')" case else rcddetail = rcddetail & rngtblrcds.rows(tempcl).columns(ch).value & "','" end select end select next ch tempcl = 0 goto skipads end if notnull = false rcddetail = "('" ch = 1 rngcolheads.count select case rngtblrcds.rows(cl).columns(ch).value case = empty select case ch case = rngcolheads.count rcddetail = left(rcddetail, len(rcddetail) - 1) & "null)" case else rcddetail = left(rcddetail, len(rcddetail) - 1) & "null,'" end select case else notnull = true select case ch case = rngcolheads.count rcddetail = rcddetail & rngtblrcds.rows(cl).columns(ch).value & "')" case else rcddetail = rcddetail & rngtblrcds.rows(cl).columns(ch).value & "','" end select end select next ch skipads: select case notnull case = true rs.open "insert " & tblname & colhead & " values " & rcddetail, conn case = false 'do not insert record end select next cl endupdate: if err.number <> 0 on error resume next conn.rollbacktrans msgbox "there error. exit macro.", vbcritical, "error!" end else on error resume next conn.committrans end if conn.close set rs = nothing set conn = nothing on error goto 0 skipexport:
everything looks ok, here's i'd try.
manually type path dbpath, entire connection string. perhaps path isn't being populated correctly.
here's connection string can follow:
provider=microsoft.ace.oledb.12.0;data source=c:\myfolder\myaccessfile.accdb;persist security info=false; if doesn't work, double check table name correctly named. if checks out, try wrapping table name in []. so:
tblname = "[wait_data_table]" these common things give me grief, perhaps 1 of these happening well.
Comments
Post a Comment