I want to write some small programs to automate tasks between Excel and Access. Should I use VB or Java?

Question

I do a ridiculous amount of running queries in Access, copying and pasting data into specific cell ranges in new date-named worksheets in the same workbooks every week. To add to the issues, I pull data into my Access database using ODBC-connected tables that require me to login to the server with the same login info using the Linked Table Manager everytime I load my Access. The copying and pasting to specific cell ranges in specific worksheets in one workbook. I need to automate as much of this as possible. I already know Java and know that Java could simplify a lot of the repetitive tasks, using a good API. However, I want to avoid having to go through downloading the files, programs, etc. necessary to run Java apps on my work computer, as I want other co-workers to be able to utilize the same program that I come up with on their computers without a lot of hassle. I don't know VB and am still unclear if this is something I should learn that will work similar to Java.

Answer 1

It depends on what database driver the ODBC connected tables are to. If it is a MS product (more likely the case), then VB and VBA are what I would recommend. The libraries would be easily available in VB to drive and automate applications to connect in this fashion.

If you find it is non Microsoft, then I would use Java, which means you will need to potentially write your own application to perform the functions stated. Java does not really easily integrate into MS applications

Answer 2

I would strongly suggest using VB, simply because both Access and Excel are Microsoft products, and there are several libraries included with VB (.Net anyway) that allow for fairly easy linking to both the Access and Excel object models. If you know Java, you should be able to pick up VB quite quickly. It is more literal than Java, more wordy, and if anything, easier to program in. You might also consider putting the code directly in Access, in the VBA behind the forms and reports. Most often, in industry, that is the method I have seen used for working between Excel and Access. By the way - if you are having to login to the db every time you access the linked tables, it sounds like they may have been linked without the "remember password" box checked. When setting up the linked tables, there is screen that comes up when setting the ODBC connection that has a small checkbox in the bottom left side that says "remember password" or something like that. If you go back through the process, and check that box, you shouln't have to login every time. I experienced the same problem, and found it easier to spend the time recreating the links than to have to login every time. Good luck - if you need any assistance, I'd be glad to help : )

Answer 3

Use Microsoft Acess and vba.
if you want total control over what gets exported to excel, use vba code. Include the Microsoft Excel Object Library in References so you are able to read and write excel files in a module.

heres a code snip that might get you started:
ex:
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

' Export a recordset to an Excel file
' mb - November, 2001

Dim CName As String
Dim X As Integer
Dim c As Column
Dim fld As Field

rs.MoveLast
maxrows = rs.RecordCount
rs.MoveFirst
myrow = 1
mycol = 1
Screen.MousePointer = 11

' load Excel
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add
xlSheet.Name = WkBookName

' Output the field names
maxcol = rs.Fields.Count
For mycol = 0 To maxcol - 1
Set fld = rs.Fields(mycol)
xlSheet.Cells(myrow, mycol + 1).Value = fld.Name
Next

' Output the data
myrow = 1
(remainder of code is not shown)
You'll need to just bump through each record in your recordset (rs), retrieve the field data, and write it to the excel file.


Or -
Use the "TransferSpreadsheet method " in ms access if you just want to move ms access query results to Excel with no checks and balalces..

Answer 4

Between Microsoft products, your best bet will be to use VBA, VB, or C#. You can use Java working with a Java to COM bridging framework (JIntegra for example) but it definitely adds a layer of complexity. Unfortunately the errors thrown in the Java/COM bridging are not very useful (at least based on my experience).

Answer 5

Hi,
this should be easy just use "VBA" in your excel file, search the help files for using "ADO" links, which will let you connect to other programs remotely and select what data you want and paste it where you want as well all for you.. hope this all helps you. a good book is MS Office "Inside out" if you need more help.

Answer 6

Hey, i'd use vb in this example. Keep it nice and simple and you cant go wrong.