{"id":518,"date":"2011-11-29T09:47:36","date_gmt":"2011-11-29T08:47:36","guid":{"rendered":"http:\/\/www.capri-soft.de\/blog\/?p=518"},"modified":"2011-11-29T09:56:41","modified_gmt":"2011-11-29T08:56:41","slug":"vba-und-access-datenbankzugriff-auf-mdb-von-vba","status":"publish","type":"post","link":"https:\/\/www.capri-soft.de\/blog\/?p=518","title":{"rendered":"VBA und Access : Datenbankzugriff von VBA auf .mdb-Datei"},"content":{"rendered":"<h1>Aufgabenstellung<\/h1>\n<p>Von einer Office\/VBA-Anwendung aus, soll auf eine Microsoft Access Datenbank (.mdb-Datei) zugegriffen werden.<\/p>\n<h1>Vorraussetzungen<\/h1>\n<p>Im VBA Editor im Men\u00fcpunkt Verweise wird die Option Microsoft ActiveX ADO Objects angehakt. <\/p>\n<h1>L\u00f6sung<\/h1>\n<h3>Selektionsanweisung<\/h3>\n<pre class=\"brush: jscript; title: ; notranslate\" title=\"\">\r\nPublic Sub HoleDaten()\r\n   Dim cn As New ADODB.Connection\r\n   Dim rs As New ADODB.Recordset\r\n   Dim i As Integer\r\n\r\n&#039; Datei liegt im aktuellen Projektverzeichnis\r\n   cn.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; &amp; CurrentProject.Path &amp; _\r\n                 &quot;\\diedatei.mdb&quot;\r\n\r\n   Set rs = cn.Execute(&quot;SELECT * FROM einetabelle&quot;)\r\n\r\n   i = 1\r\n   Do While Not rs.EOF\r\n      Cells(i, 1).Value = rs.Fields(&quot;Feld1&quot;)\r\n      Cells(i, 2).Value = rs.Fields(&quot;Feld2&quot;)\r\n      Cells(i, 3).Value = rs.Fields(&quot;Feld3&quot;)\r\n      Cells(i, 4).Value = rs.Fields(&quot;Feld4&quot;)\r\n      rs.MoveNext\r\n      i = i + 1\r\n   Loop\r\n   cn.Close\r\nEnd Sub\r\n<\/pre>\n<h3>Manipulationsanweisung<\/h3>\n<pre class=\"brush: jscript; title: ; notranslate\" title=\"\">\r\nPublic Sub SchubseInMDB()\r\n   Dim cn As New ADODB.Connection\r\n   Dim rs As New ADODB.Recordset\r\n\r\n   cn.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=unfug.mdb&quot;\r\n\r\n   Set rs = cn.Execute(&quot;INSERT INTO einetabelle(zweck)VALUES (&#039;Unsinn&#039;)&quot;)\r\n   cn.Close\r\nEnd Sub\r\n<\/pre>\n<iframe src=\"http:\/\/www.facebook.com\/plugins\/like.php?href=https%3A%2F%2Fwww.capri-soft.de%2Fblog%2F%3Fp%3D518&amp;layout=standard&amp;show_faces=true&amp;width=450&amp;action=like&amp;colorscheme=light\" scrolling=\"no\" frameborder=\"0\" allowTransparency=\"true\" style=\"border:none; overflow:hidden; width:450px;margin-top:5px;\"><\/iframe>","protected":false},"excerpt":{"rendered":"<p>Aufgabenstellung Von einer Office\/VBA-Anwendung aus, soll auf eine Microsoft Access Datenbank (.mdb-Datei) zugegriffen werden. Vorraussetzungen Im VBA Editor im Men\u00fcpunkt Verweise wird die Option Microsoft ActiveX ADO Objects angehakt. L\u00f6sung Selektionsanweisung Public Sub HoleDaten() Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim i As Integer &#039; Datei liegt im aktuellen Projektverzeichnis cn.Open &hellip; <a href=\"https:\/\/www.capri-soft.de\/blog\/?p=518\" class=\"more-link\"><span class=\"screen-reader-text\">VBA und Access : Datenbankzugriff von VBA auf .mdb-Datei<\/span> weiterlesen <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[16,10,12],"tags":[],"class_list":["post-518","post","type-post","status-publish","format-standard","hentry","category-access","category-datenbanken","category-vba"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p4yGeN-8m","jetpack_likes_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/518","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=518"}],"version-history":[{"count":6,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/518\/revisions"}],"predecessor-version":[{"id":520,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/518\/revisions\/520"}],"wp:attachment":[{"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=518"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=518"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=518"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}