Use VBA to update Access table or Query from Excel

Can I use VBA to update Access table or Query from Excel?

Thanks in advance
0
Utf
3/4/2010 8:35:02 AM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
3954 Views

Similar Articles

[PageSpeed] 48

Hi Leungkong,

> Can I use VBA to update Access table or Query from Excel?

Of course, using ADO or DAO.
See:

http://www.erlandsendata.no/english/index.php?d=envbadacexportado

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

0
Jan
3/4/2010 9:07:10 AM
Hi Jan,

Thanks. I think ADO is what I want.
But I am not only want to export from excel to access. 
I want to edit some data in access table.

For example, Access has a table "ProductList"
I want to use Excel to call the product by "ProductCode" field.
After I update the "UnitPrice" via excel to access.

Can I use ADO to do it? If yes, could you please advice me some book or 
link? Thank you very much.

Kind Regards,
Kong


"Jan Karel Pieterse" wrote:

> Hi Leungkong,
> 
> > Can I use VBA to update Access table or Query from Excel?
> 
> Of course, using ADO or DAO.
> See:
> 
> http://www.erlandsendata.no/english/index.php?d=envbadacexportado
> 
> Regards,
> 
> Jan Karel Pieterse
> Excel MVP
> http://www.jkp-ads.com
> 
> .
> 
0
Utf
3/16/2010 10:32:01 AM
Hi Leungkong,

> For example, Access has a table "ProductList"
> I want to use Excel to call the product by "ProductCode" field.
> After I update the "UnitPrice" via excel to access.
> 
> Can I use ADO to do it? If yes, could you please advice me some book or 
> link? Thank you very much.

The information is in the same link I gave previously.

There is a microsoft press book called "Programming ADO" by Sceppa which is 
quite good.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

0
Jan
3/16/2010 5:59:58 PM
Reply:

Similar Artilces:

how do I add times in Excel and result in hours & mins
I want to insert a time when I start work and a time when I take a break, then a time when I leave work. Following that I want to be able to add up the amount of hours that I have worked. This will enable me to plan my week ahead and ensure I only allocate a specific amount of time to a project. http://www.cpearson.com/excel/datetime.htm#WorkHours -- Kind Regards, Niek Otten Microsoft MVP - Excel "Rty Shaw" <Rty Shaw@discussions.microsoft.com> wrote in message news:37D03D72-5525-4D6E-8ED7-2911B16248B0@microsoft.com... >I want to insert a time when I start work and...

Illegal operation error while printing EXCEL or WORD Files
Hi, I am facing an illegal operation error when i try to print any file from excel (any no. of pages), this happens in stand alone printer as well as a networked printer. When we press the print button, it flashes this message, but still prints, but once the printing is completed, i will have to restart the PC. Due to this error other applications PRINTING also will NOT HAPPEN and the only way out is, restart the PC. This happens not only in EXCEL, it happens in all the MS applications (outlook, access, front page, powerpoint also). When I check the print manager (before restart),...

VBA from another app: Suppressing Excel confirmation dialog?
After creating/formatting several worksheets from MS Access, I'd like to delete the "Sheetn" worksheets that got put there when I did a .WorkBooks.Add. I avoided using them because I'm not sure how/why they are created - i.e. maybe some user's defaults would only create 1 empty sheet or none. So, form MS Access's VBA I'd like to do: On Error Resume Next .Worksheets("Sheet1").Delete .Worksheets("Sheet2").Delete .Worksheets("Sheet3").Delete .Worksheets("Sheet4").Delete On Erro...

Importing Data into an Excel Pivot Table via Access
I have set up a query in Microsoft Access which is linked to our AS400 server. I have created pararmeters within Access which asks for certain fields which works. I then go into Excel and create a pivot table with the external data source that I have created in access. When I go to enter a pararmeter within Microsof Query I get a reply saying that "Parameters can not be used with this Query", what I want to do is setup a parameter on the Excel spreadsheet which then goes and gets the data i require from this parameter. I would be very grateful if someone could help me with thi...

RE: Use that security update
--dedtgpho Content-Type: multipart/related; boundary="yzkfzsfqtv"; type="multipart/alternative" --yzkfzsfqtv Content-Type: multipart/alternative; boundary="qeviqcxczwaquy" --qeviqcxczwaquy Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Consumer this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to hel...

Auto sort dat when updated
I am not a novice with Excell, however I have never written a macro. I have a worksheet that data can be updated on. That data transfers by formula to another worksheet. I want the second worksheet to auto sort when data is updated. I believe I need a macro to do this, but I don't know how to even start. You can sort the data using formulae. There are lots of examples in the archives if you do a Google search, but if you can't find anything that suits then you will need to provide more details of what data you have, how it is organised etc. Pete On Jan 13, 7:36=A0pm, bvander...

REPOST: Looking for how to use DTPicker
I am using Excel XP and am trying to use the date/time picker. Is there a way to use this that will give me date AND time as a single field? Is there a way (Or where can I get code to do so) to have the DATE/TIME validated with OUTLOOK-Calendar to see if that DATE/TIME is free? I quess I would need a duration as well? Also posting this in OL and Excel groups... Thanks BPJ Wrong newsgroup -- and most people don't answer pests who like to crosspost like crazy. It is very inconsiderate. "Newbie1" <newbie1@No.SPAM.com> wrote in message news:Lthcc.191900$Cb.173228...

Opening a new instance of Excel
I am using multiple monitors for work and it is great! Is there a setting that I can use so that it opens each new excel file in a new excel window so I can drag different ones to each monitor? Is there a similar setting for Word? I am using Excel 2002 and Word 2002. Thank you. Hi, Yes, you can check the Windows in Taskbar checkbox in Tools; Options. This is on the View tab for both Word and Excel. >-----Original Message----- >I am using multiple monitors for work and it is great! Is >there a setting that I can use so that it opens each new >excel file in a new excel ...

re: updating values
that works, but i'll need to add a lot of hidden feilds (20+/-)... Is there another way (perhaps more efficient -if not as simple?) ("there's more than one way to skin a cat") thanks inadvance, mark --------------------------------------------------------------------- "Daryl S" <DarylS@discussions.microsoft.com> wrote in message news:79CFD708-34B3-419A-A3F1-CF7050ACDE9F@microsoft.com... > Mark - > > Add the field [PresetOption] to the form. You can set the .visible > property > to FALSE so the user won't see it. Then the code...

Emailing in excel 2003 02-26-10
If i type in the cell A34: neil.Holden@test.com and press a button is it possible to email to the address of what ever is in A34 is? The email body should say: this has been submitted for cell B34 and todays date. Thanks. Check out Ron De Bruins "Send-Mail" tips: http://www.rondebruin.nl/sendmail.htm Micky "Neil Holden" wrote: > If i type in the cell A34: neil.Holden@test.com and press a button is it > possible to email to the address of what ever is in A34 is? > > The email body should say: this has been submitted for cell B34 and...

using GP with Cognos
Is it possible to map GP to a reporting tool like Cognos? If so, what would be the easiest way to decipher to the data tables? Thanks for your help. The first thing is to install the SDK from the second CD. You need to explore the disc and look for the tools folder then the SDK folder. When you install the SDK, you will get a new item in your Dynamics program group. You will have access to documentation on data flow. Additionally, within GP you have the resource descriptions off the Tools menu. Richard Whaley has books that can help you, as well. Go to www.accoladespublications....

VBA Form Position
Here is a question for the community on VBA - I am puzzled and would love some input as to why this behavior: I am trying to open up a separate form to display some additional attributes when Item Maintenance is opened. This form is opened right below the item maint form, has the same width as item maint and about an inch and a half tall, and displays attributes from a custom table. User may move the item maint form and I am moving this custom form along with it. I am using the left and top properties of the Item Maintenance form. I then add the item maintenance form's he...

Outlook Mobile Access Problem
Hi I've a big problem in Outlook Mobile Access. I've a server exchange standalone withoput a front-end=20 one... I've configured the OWA web form authentication and SSL. For OWA it's all right...everything is going right... The problem is OMA When I try to log on on oma I find the following error=20 message :"Unable to connect to your mailbox" To solve my problem I've found in microsoft web site the=20 following link Microsoft Knowledge Base - 817379 Cannot Access Exchange Server 2003 by Using Outlook Mobile=20 Access When the Exchange Virtual Directory Require...

make column lists for select query.
sheet1 table1 col1_1 table1 col1_2 table1 col1_3 table1 col1_4 table1 col1_5 table1 col1_6 table2 col2_1 table2 col2_2 table2 col2_3 table2 col2_4 .... .... sheet2 table1 col1_1,col1_2,col1_3,col1_4,col1_5,col1_6, table2 col2_1,col2_2,col2_3,col2_4, .... .... I want to make column lists for some table listed in sheet1. for example, select column_lists from table1 without vba is it possible? thanks. I think you may want some dependent lists. http://www.contextures.com/xlDataVal02.html HTH, Barb Reinhardt "kang" wrote: > sheet1 > table1 col1_1 > table1 col1_2 >...

How do i get outlook to update hotmail account without reopening?
the tedster <the tedster@discussions.microsoft.com> wrote: <nothing> Ask your question in the body of the message. Without reopening what? Outlook? Hotmail? The short answer is "you can't". -- Brian Tillman ...

Excel Crash
I use Excel and Word 2003 using Windows NT. I've kept some files on a jump drive so I can work on them at home. I attempted to work on a Word documents which had an Excel worksheet inserted in it. I tried double clicking on the worksheet to edit it and Word and Excel shut down. Now when I attempt to open Excel at home it asks for my Office XP Professional installation cd. (I have Office XP at home with Windows XP). I'm having a hard time locating my original discs. Does anyone have any suggestions or experience anything like this? ...

Does anyone have a dashboard gauge (speedometer style) for Excel?
I am trying to create dashboard charts from Excel data and would love other templates not available in Excel today - speedometer charts, multi-dimension comparitive charts, charts that build information overlays. I regularly create these in a manual way for executive and customer summaries but would appreciate the ability to automatically generate these types of charts allowing for real time viewing of "what if" scenarios. Steve, there are tons of these things out there to review, few better than this collection: http://www.andypope.info/charts.htm Andy Pope has put together...

Dates in a form for filtering Report query
I have a form "Period" with two text boxes. One for startDate and one for EndDate. I want to use that form to limit the query for my Report by the dates. However, when I refer to the Form it does not seem to understand it is a date? I use the following statement in my query: SELECT Opphold.CheckIn FROM Opphold WHERE (((Opphold.CheckIn)<=[Forms]![Perioder].[txtStartDate])); I also tried to convert it to a date like the following: CDate(<=[Forms]![Perioder].[txtStartDate]))) but that did not work? What should I do in order to the query to read the condition or dat...

Parent / Child Price Updates
Is there an add-on that will update the cost of a "child" item when the "parent" item is purchased? This is a multi-part message in MIME format. ------=_NextPart_000_002A_01C705BB.BB763D20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable This is what already happens! Whenever u do receiving by a parent, the = child cost is updated! This might happen to u if you have more than one item that is assigned = to the same parent item, in this case, the cost of one child only will = be updated, this's a bug that was reso...

How can Journal be used if Project is not installed, or on the net
How can Journal be used if Project is not installed, or on the network -- Rusty Nichols Network Support Technician The Journal folder works without Project being installed at all. It's an integral part of Outlook. What functionality are you seeking? "Rusty" <w_r_nichols_iii@yahoo.com> wrote in message news:7EA79673-AE35-44D2-A09E-EBF73E7EC414@microsoft.com... > How can Journal be used if Project is not installed, or on the network How can Journal potentially affect the Exchange server, and I would like more information on Journal for a Outlook foundation class? ...

Excel Edit F2 button changed for Mac???
Switched to Microsofts version of Excel for Mac. Can anyone tell me what keystroke allows me to edit a cell? Before I switched to a Mac it was the F2 button. Please help. Thank you. See the answers in the m.p.mac.office.excel newsgroup. In article <1176582208.958694.269620@q75g2000hsh.googlegroups.com>, ssears@indy.tds.net wrote: > Switched to Microsofts version of Excel for Mac. Can anyone tell me > what keystroke allows me to edit a cell? Before I switched to a Mac > it was the F2 button. Please help. Thank you. ...

EXCEL TROUBLESHOOTING #2
I have an excel file (2000 format), that after I made a number of changes is causing me problems when I re-open the file. Windows task manager goes to 100% CPU activity, and i cant do anything within the excel file. However, if I set recalculation to manual before I open the file, all seems fine. Obvioulsy I have a problem. But how do i find that problem ? Thanks in advance. I have had some experience running large spreadsheets lately. Above a certain size, the recalculation time seems to climb very fast. While Excel is recalculating, you can't do anything anyway. Best in my v...

Chart Stopped Updating
Hi I have a number of DDE links that I use to increment Rows D to I every second, providing me with a method of data logging my DDE Links. I find that my chart works fine up to about 247 rows then stops updating. I have just upgraded from 2003 to 2007 version of Excel. Any ideas. Thanks Alec when you saved into 2007, you keep the old file format or use the new format? if using new, pls make sure you are using xlsm. if you are using the compatibility mode, please check make to make sure you click the security setting to low. "Alectrical" wrote: > Hi ...

How do I create a pivot table if the pivot table icon or menu ite.
If rhe pivot table icon ...??? Please clarify in the body of the message. "Lynn@WS" <Lynn@WS@discussions.microsoft.com> wrote in message news:0E6B098C-551A-4389-9048-7F4F5A6E5EF8@microsoft.com... > ...

"floating" date query
I have a tblPersonalInfo containing information including dtmHiredDate and strJobTitle. I have another tblPromotions containing info including dtmPromoDates and strPromoTitle. This table may contain duplicate strFirstName and strLastName because of different dtmPromoDates and strPromoTitles (because same person may have multiple promotions). Another tblJobTitle_JobRates lists strJobTitles and curJobRates. A final tblHoursWorked contains information including dtmPayEndDate. I am having difficulty writing a query that would extract dates based on HoursWorked with the appropriate JobTi...