Free Microsoft Excel 2013 Quick Reference

Read/Write Access DB from VBA in Excel

The code below is a portion of a sample I found online. It reads the database table and puts in excel. The problem is that I
cannot write to the database. When I try to add rst1.Fields(0) = "Bob" I get an error 3251. What do I need to do to be able to write to this DB?

Sincerely,
Bob Hiller
Lifts for the Disabled LLC

Sub Import_AccessData()
Dim cnt As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim stDB As String, stSQL1 As String
Dim stConn As String
Dim wbBook As Workbook
Dim wsSheet1 As Worksheet
Dim lnField As Long, lnCount As Long

'Instantiate the ADO-objects.
Set cnt = New ADODB.Connection
Set rst1 = New ADODB.Recordset

Set wbBook = ThisWorkbook
Set wsSheet1 = wbBook.Worksheets(1)

'Path to the database.
stDB = "c:db1.mdb"

'Create the connectionstring.
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & stDB & ";"

'The 1st raw SQL-statement to be executed.
stSQL1 = "SELECT * FROM table1"

'Clear the worksheet.
wsSheet1.Range("A1").CurrentRegion.Clear

With cnt
.Open (stConn) 'Open the connection.
.CursorLocation = adUseClient 'Necessary to disconnect the recordset.
End With

With rst1
.Open stSQL1, cnt 'Create the recordset.
Set .ActiveConnection = Nothing 'Disconnect the recordset.
End With

With wsSheet1
.Cells(2, 1).CopyFromRecordset rst1 'Copy the 1st recordset.
End With

rst1.Fields(0) = "Bob"

'Release objects from the memory.
rst1.Close
Set rst1 = Nothing
cnt.Close
Set cnt = Nothing
End Sub


Post your answer or comment

comments powered by Disqus
If you are working through the example, "Building a Pivot Table with Microsoft Access Data" (starts on pg 180 in Pivot Table Data Crunching) you will run into issues with the Access DB on the Mr. Excel site.

If you download the Access DB from the Mr Excel Site http://www.mrexcel.com/PT2007Chap7DB.zip the Sales_by_Employee query will not be displayed in the Select Table dialog box after you click the "From Access" button in the Excel Ribbon.

The Sales_by_Employee query in the access DB requires parameters and hence will not display as a "view" in the "Select Table" dialog.

To fix this issue, you will need to go Access DB and create a new Sales_by_Employee query that does not require any parameters.

Please fix the Access DB Mr. Excel and re-post to your site.

Thx.

I want to call an existing excel 4.0 macro in a macro written in vba in excel 2002
The excel 4.0 macros are quite complex so I don't want to convert them
any ideas?

Hi: First thing to metion is that the result field I expect is a Memo Field
When I run the following (qry*)query in access I do get the expected one (row*)row as the result-However when I use this (modl*)code into an Excel VBA module-nothing happens- I tested for recorcound and it does show -1. Just wondering why I Am not getting the results when using VBA in Excel-Thanks in advance
****** IN ACCESS THIS WORKS FINE **********
(qry*)
SELECT Periods_Desc FROM FCST_Template_SGA GROUP BY Pull_Date, Template_Name, Periods_Desc HAVING Template_Name="AFTRHRS";
(row*)
JAN 08 ACTUAL-FEB 08 ACTUAL-MAR 08 ACTUAL-APR 08 ACTUAL-MAY 08 ACTUAL-JUN 08 ACTUAL-JUL 08 ACTUAL-AUG 08 ACTUAL-SEP 08 ACTUAL-OCT 08 6+6 FCST-NOV 08 6+6 FCST-DEC 08 6+6 FCST-
******HOWEVER IN EXCEL IT DOES NOT DISPLAY ANYTHING ****
(modl*)
Private Const cDir_Database As String = "C:Documents and SettingsBLABLAMRA_Database.mdb"

Public DB_Conn As ADODB.Connection 'Access connection
Public DB_RSet As ADODB.Recordset 'Access Record Set
Public DB_SQL As String 'SQL Commands
'======
Private Sub UserForm_Initialize()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Set wbBook = ActiveWorkbook
Set wsSheet = wbBook.Worksheets("SGA")
OPEN_DATABASE
'*** HEADER
DB_SQL = "SELECT Periods_Desc FROM FCST_Template_SGA GROUP BY Pull_Date, Template_Name, Periods_Desc HAVING Template_Name='" & "AFTRHRS_SF_UBH" & "';"
With DB_RSet
Application.StatusBar = "Checking Valid Source Codes..."
.Open DB_SQL, _
DB_Conn, _
adOpenForwardOnly, _
adLockReadOnly 'Open the list of Source Codes
MsgBox CStr(.RecordCount)

wsSheet.Range("A1").CopyFromRecordset DB_RSet
.Close
End With
CLOSE_DATABASE
End Sub

'===
Public Sub OPEN_DATABASE()
Application.StatusBar = "Connecting to the Database..."
Application.Cursor = xlWait
Set DB_Conn = New Connection 'Database Connection
Set DB_RSet = New Recordset 'Database RecordSet
DB_Conn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & cDir_Database
DB_Conn.Open
DB_Conn.BeginTrans

Application.StatusBar = False
End Sub
'=====
Public Sub CLOSE_DATABASE()
Application.StatusBar = "Disconnecting from the Database..."
DB_Conn.CommitTrans 'Commit All Work
DB_Conn.Close 'Close the connection
Set DB_RSet = Nothing

Application.Cursor = xlDefault
Application.StatusBar = False
End Sub

Hello,

due to performance issues, I would like to query an ADODB record set.

So, first I excecute a simple query from VBA in Excel to an Access database with an ADODB connection. Then I would like to perform about 20 queries to this recordset.

I tried to make one intelligent query to do this in one query, but after some investigation, this seems to be impossible with Access.

So, can I query an ADODB recordset?

thx! grt, Bram

I need to query data from DB via VBA in Excel. This step will costed a few of
seconds. End-User will only wait for result without action. Maybe this time
of this section may be long time. Does VBA provide a way to pop-up a window
which included some message to notice End-User the programmer is running. For
example, When I click a button which query data from DB, a window will appear
with no buttion, it will disappear automatically when the data is retrieved.
--
Developer
QA Dashboard
Microsoft China Development Centre

Hello,

I am interested in programmatically creating a map from data in Excel. I
have some GIS data on customer site locations that I would like to
periodically generate a map of.

What is the best path?
A) Write a macro (in VBA) in Excel that could generate a map of the circuits.
or
B) Write a stand alone app (my pref for language is C#)

Also, I noticed that DataMap is not in Excel 2003. Is using MapPoint the
preferred product to use for mapping?

Any thoughts?

Thanks,
David

Morning All..........

I have an access database that has many queries and in order to speed it up I have a Macro in access that runs it in no time, but I need to be able to run the query in access via a trigger in Excel and have come up with the below, but it's not working, any help would be great?!?!?

Craig

======================================================

Sub Run_Access_Qry()

'This executes the Macro everyday @ the specified times

Application.OnTime Date + TimeValue("07:54:00"), "ExecuteMacro"
'Application.OnTime Date + TimeValue("14:49:00"), "ExecuteMacro"
'Application.OnTime Date + TimeValue("14:50:00"), "ExecuteMacro"

End Sub

Sub ExecuteMacro()

'This is running the access Macro
'Craig you need to make sure Tools>References>"Microsoft DAO 3.6 Object Library" is checked

Dim qdf As DAO.MacroDef, db As DAO.Database

Beep 'This confirms that the automation has taken place

Set db = OpenDatabase("C:Documents and SettingscraigMy DocumentsDatabase's & ProgramsPM_Comments.mdb")

Set qdf = db.MacroDefs("Re-Create CMS Tables & Refresh PM Report") 'Insert Macro name

qdf.Execute

Set qdf = Nothing
db.Close
Set db = Nothing

End Sub

Hello ! I am designing an application to access a PBX using serial
connection. I want to access Hyperterminal from VBA, in order to
connect the PBX using a dial up connection. How can I set up, activate,
and send and receive data from modem, using VBA ?

Thanks in advance.

LQ

I am trying to make a DLL using VC++ 2005, and use the DLL in VBA. Here is my C++ code:

Main.cpp

extern "C" long __stdcall Pow(long x, long n, long P)
{
   ...
   return ...;
}
Lib.def:
LIBRARY "Lib"
EXPORTS
   Pow @1
Here is my VB code:

Private Declare Function Pow Lib "Lib.dll" _
  (ByVal x As Long, ByVal n As Long, byVal P As Long) As Long

...
Public Function ComputePow(ByVal x As Long, ByVal n As Long, ByVal P As Long) As Long
   ComputePow = Pow(x, n, P)
End Function
In excel, the usage of ComputePow comes up with #VALUE!, and stepping through the code it ends on the line calling Pow (in my real code there is more, and it works up until that line).

The dll is in the same folder as the worksheet.

What am I doing wrong?

I'm writing a macro with VBA in Excel 2003 and I would like to use the
Queue class from VB.Net. Is there a way to do this? Thanks.

Scott

hi guys once again
so again i have question which might be very simple but for me its difficult so can any body tell me that how i create a new worksheet using VBA in Excel 2003
waiting for urgent response
wiz

Does anyone know if it is possible to call Row() using VBA in Excel? When I look through WorksheetFunction. in Excel it does not list the Row() and it errors out if I try to code it.

Is there a good example or tutorial on how to call a .dll (one i wrote) from VBA in Excel?

thanks
Fred

Please can someone help me. How do I protect specific cells from changes in
Excel? I have a spreadsheet that I would like users to only be able to change
certain cells, the others need to be protected from change

How to send post data using VBA in Excel 2007 with WinHTTP?
How can I send multiple post fields?

I need some help modifying VBA in excel.... i can pay through paypal....

please contact me ASAP at conspiringfate@earthlink.net

Here is a very general question--I hope not too general!

In Excel, I want VBA to refer to the SENT ITEMS folder in Outlook. I want to loop over all sent items, and if the email address satisfies a condition, then record that fact in Excel.

I am good at VBA in Excel, and I have a passing knowledge of VBA in Outlook, but I don't know how to refer to Outlook within my Excel VBA.

Thanks for any suggestions!

Hi, I'm trying to use Clipboard from VBA in Excel 2000.
I can paste a single cell from the Clipboard using DataObject and related
methods (MSForms 2.0 obj library) but I can't manage a range of cells.
After selecting and copy a range of cells from Excel to the Clipboard, with
Clipboard Viewer I can see only one text string (from the last cell) and no
formulas.

Should I try with WinAPI calls??? I searched Internet and I've found a tool
(API Viewer) to look at the declares statement of a DLL, i.e.:
Declare Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As Long
but I've never used it before.
I'd like to do a macro that can check if the Clipboard contains a range from
Excel and then make an "exact copy" of all the range formulas into a new
worksheet / workbook without formula address changes.
Thank you for any suggestion ????

Hi All,

Does anyone know if it is possible (and if so how?) to run a .exe file from VBA in excel? Specifically, I would like to add a command button to my worksheet and when it is pressed it runs a .exe file which is in the same folder as the excel workbook. The .exe file will not be used in any way by excel; it will only display information for the user.

Any help would be appreciated.

Best Regards,

Aaron

Is it possible to hide selected cells within a workbook from printing in
Excel 2003 and how can this be done?

Regards

Nevio

How do I determine my computers IP address through VBA in Excel

Does anyone knows how can i do this???

I am just starting to learn programming and wanted to get a few suggestions on some of the best books/websites for learning VBA in Excel. Its finally high time i got some literature on it... Thanks..

Hi,

I would like to know if it is possible to create a histogram from data
in excel. I have approx. 30000 values in a column that varies from
0,001030093 to 276,9992477.

It would be great if I could get a bar chart with like 5 bars where
each bar shows the number of values within a particular range for
example 0-2, 2-4,4-10, 10-100,100-300.

I've tried to use the program Minitab but I cant seam to get it
right.

Hi all!

I'm Kristoff, a student bachelor Logistics in Belgium. For my final thesis, I'm trying to make a automated Excel so people from a company can calculate time measurements. Multi-moment recording to be more specific. At this moment however, I'm stuck.
For one of the calculations, I've to select the maximum percentage of all. That's no problem. But in another sheet, I have to use information that has to be auto-selected from the other sheet, together with other information. I don't know if it is possible... I have some knowledge with Macro's in Access but from VBA in Excel I know nothing. I added a Excel file with 2 sheets, the table and on the second page the biggestFA. At this moment, I just pasted the correct one there, but I have no idea how to automate it. Probably more questions will raise and I will be very happy if you could help me!

Thanks in advance!


No luck finding an answer? You could always try Google.