I have a DB that has subforms in it... I can't determine how to use Analyze with Microsoft Excel option and get it to get me my complete data... subforms info included... :confused: this is what I have right now...
Private Sub OutputToExcel_Click()
On Error GoTo Err_OutputToExcel_Click
Dim stDocName As String
stDocName = "Output to Excel"
DoCmd.RunMacro stDocName
I am trying to find the best way to compare estimated expenses to actual expenses in Access. I am working with vessel and port call information, so each port call has a series of costs associated with it. I have two tables, each with multiple fields (around 100). Each of the fields contains a cost type that goes to a particular cost code. Each row of the table is associated with one particular port call. The call is estimated right after it happens, but the invoices are not all recieved for up to three months. Once all of the invoices for this port call have been recieved, we enter the actual expenses for the call. I am trying to find the best way to analyze the costs through finding the variances for one particular port call for each code, and also for multiple port calls to see which cost item has the largest/smallest variance for further investigation into how to estimate it more accurlately. I am thinking that I may have to move into Excel to do this, but would like to stay in Access if possible.
I have an excel spreadsheet with 8 tabs. They are all in the same format and column order. They are employees grouped by region. My ultimate goal is to merge all of these onto one excel tab, relatively instantly. I created a master tab and tried doing array formulas and Vlookups, it worked but my spreadsheet was way too slow.
My solution? Import and link them to an Access database, step complete. Create an XML export then import into Excel.
My problem? The only way to update the excel tab with the combined tabs is to save the excel file after changes, go back into Access, re-export to XML, then go back into excel and refresh the data.
My questions, is there any way to automate this process to the point that I can change excel, save, then hit refresh on my excel tab with the XML import to auto-update?
Not sure if this has been discussed already, forum searches yielded no results:
I have an mdb that I have ported to SQL server. I would like to continue using Access as the front-end, and have begun to re-write the GUI using ADO. While it seems that most operations can be replicated using ADO, I am having trouble with subforms, especially those where the users currently enter more records i.e. inputting order lines which correspond to a single order record. (They enter the order line records into a subform setup as a datasheet).
So far, I have been able to create a form accesing the "Orders" table, and a subform accessing the "OrderLines" table, however, the orderl lines appear as read-only and no input is allowed (the usual 'empty' record is not present).
Can anyone advise me on how to proceed?
I would like to retain the simple method of entering order lines into the datasheet as the users are familiar with it, but if there's a better way or only certain methods are allowed, I'd like to know.
Not sure if this has been discussed already, forum searches yielded no results:
I have an mdb that I have ported to SQL server. I would like to continue using Access as the front-end, and have begun to re-write the GUI using ADO. While it seems that most operations can be replicated using ADO, I am having trouble with subforms, especially those where the users currently enter more records i.e. inputting order lines which correspond to a single order record. (They enter the order line records into a subform setup as a datasheet).
So far, I have been able to create a form accesing the "Orders" table, and a subform accessing the "OrderLines" table, however, the orderl lines appear as read-only and no input is allowed (the usual 'empty' record is not present).
Can anyone advise me on how to proceed?
I would like to retain the simple method of entering order lines into the datasheet as the users are familiar with it, but if there's a better way or only certain methods are allowed, I'd like to know.
I have an unbound form on that form I want to put three sub forms one on a products table the other on a course start dates table and the link table that joins the other two together. all three are related to each other with Pk/FK links.
When I try to link them it says you cannot link items on an unbound form.
:confused: Hi everybody. I have started trying to be a little more creative and making my systems a little more what the big boss where I work would like to see. I have set up an Access Data Page, with one form on it where the user would enter some required information, which is saved into a table for me to monitor (date, time, unit, spec reviewed, name etc). What I was hoping to do, was have on teh same Page, a subform or another form, where the user would reference that other form, when doing their entry. Basically, they are entering that they looked at a certain spec so if they produce the wrong item, or use the wrong materials, we can say well, you looked at this spec, or so you say.....Does anybody have any suggestions on how this can work? Or even if it is at all a possibility. Thanks!
how i can export the data from Access to excel using Access VBA for the specified sheet using data linkage with access database. Like we used to do it manually in excel as external data from access.Like we have some codes for linking excel file to database mentioned below;
Can we have something like this to link database table in excel file automatically.So that the excel size won't be that big and also it saves processing time.
I have a stock control database which i have nearly completed. This has Manufacturer, which is linked to products, which is linked to Sub Product(which also has field partCode). i.e. Manufacturer1 can have 3 products, and each of these products could have 5 subsystems and partcodes. Each partcode is unique to that subsystem/product/manufacturer.
I then have a pricing spreadsheet in excel, which has many tabs. A new column has been added for each item for Manufacturer,Product,Subsystem and Partcode.
I need to import these manufacturers,products,subsystems and partcodes, but into the tables with the correct relationships, i.e. product1 and product2 are products of manufacturer1 and so cannot come under manufacturer2, and so on.
I hope this makes sense, Thanks in advance for any help you can give!
We currently have a spreadsheet to track all of a clients medical's bills and keep a running total? I'm trying to decide whether to continue to track these medical bills in the spreadsheet or create a table and make it a part of the client db. I'm leaning toward keeping the spreadsheet. It seems to be a task a spreadsheet was designed for and I can link it to the db or import it as needed. Any opinions on which is better? What would be the reason to to give up the spreadsheet and make it a part of the db?
I'm new to Access. I Work in excel but the Sheets start to have many records and, sometimes it's difficult to apply the formulas. My doubt is: It´s possible to make with the Access "everything" that i make with Excel. Example I apply formulas like: =IF(ISNA(VLOOKUP('[Total.xls]2006'!B2;$A$2:$AG$802;33;FALSE));"";IF(ISBLANK(VLOOKUP('[Total.xls]2006'!B2;$A$2:$AG$802;33;FALSE));"";VLOOKUP('[Total.xls]2006'!B2;$A$2:$AG$802;33;FALSE)))
Apologies if I have placed this in the wrong section but thought it was more access than excel.
I would like to on clicking a button, which simply has a close command behind it, for the database to zip the spreadsheet which it has just exported information to.
I have had a look round and tried to use the backup of database idea but could not convert it and did not know enough myself to change it.
I was wondering if anyone might be able to help me here.
I have a question. Is it possible to create new worksheet in Access using VBA code?
What happen is that i have a form in Access that would export data from Access to Excel. I know that i could use the built-in feature provided in Access to export to excel. But because i guess i want to be more flexible in managing the data on excel spreadsheet. So would it be possible to write the excel programming in the Access?
Does anyone know where can I find a tutorial for working Access with Excel? I receive regulary a spreadsheet with information that I need to copy to the access database. I can do this manualy but I would like a query that could do it for me.
Excel in Access (Part 2) (http://www.access-programmers.co.uk/forums/showthread.php?t=143607) Excel in Access (Part 3) (http://www.access-programmers.co.uk/forums/showthread.php?t=143970)
Video Version HERE: (http://msaccesshintsandtips.ning.com/profiles/blog/show?id=948619%3ABlogPost%3A7031)
How to use the Normalization Form (http://www.access-programmers.co.uk/forums/showthread.php?t=143983)
Problem for Excel Developers One of the problems facing Excel developers moving into MS Access is actually the apparent similarity between MS Access tables and Excel spreadsheets.
MS Access is NOT Excel This similarity of the “look” in both programs, the layout of the data, leads to the Excel developer mistakenly thinking that a database works in a similar way to a spreadsheet.
Flat File Database Spreadsheets are very sophisticated tools for manipulating figures, and they can also handle data very well particularly in the form of a “flat file database”. You can also use the flat file approach in MS Access, however MS Access is primarily a relational database, meaning that it has the ability to relate tables of information together.
Relational It is by taking advantage of this relational property of the database that you can really make significant improvements to data handling. So if you have a spreadsheet that requires upgrading to a database to take advantage of this, then this article is for you.
Typical Spreadsheet Layout Below is an example which shows columns that you would typically find in a spreadsheet, first name, last name for example. Then a variety of subjects, with a check box against each indicating that the subject has been taken, passed or possibly even failed!
Flat File is OK A direct transfer of this spreadsheet layout into an MS Access table would be usable and indeed many Access databases are constructed in this way, and some sophisticated applications costing many hundreds of pounds are based on a flat file system. However as mentioned earlier, MS Access is a “relational” database, meaning that it has the ability to relate your data together.
You’re Not Relational???? So how and why should you apply a Relational structure? Well it solves a lot of problems, there are many but I have noted the main ones below.
Uncharted Territory The first most common problem I have noticed on forums for people with similarly designed tables imported from a spreadsheet is that they find it difficult to extract useful information, and this is one of the major reasons that you should consider constructing your new database in a different manner to your spreadsheet, no doubt you will be able to come up with your own unique methods of extracting the data, however you will find there is little help available, not because no one wants to help you, but because you are embarking into uncharted territory, “where no man has gone before” (student is a good substitute for “man”).
Maintenance Another reason, (not in any particular order of importance, the significance will change from project to project) what happens if you need to add an extra subject? Let’s say the school starts offering French lessons, in the spreadsheet all you would do is add a new column “French” and indeed in your MS Access database you could also add a new column “French”, however if the construction of your database is well advanced, in other words you have many queries and forms based on the table you are adding a new field to, then you will have to modify every single query and form that extracts information from this table, not something you want to do often!
Column Limit Many modern spreadsheet programs can handle thousands of columns; however MS Access has a 255 limit to the number of fields in any particular table. So in the student table example, if you were at the 255 limit and you needed to add another subject then you would find yourself in a difficult position.
Relational Solution So what’s the solution? This is the part that is sort of counter intuitive, you actually construct two tables from the original table and link them together. This is the “relational” aspect of the database coming into play.
How? If you look at the original layout of the data above you can ask questions about it, is there any data in the original table that is related? Looking at it, I would suspect all of the boolean columns (the check box columns, yes/no data) they are all the same, so they are a likely candidate for a separate table. And indeed there is an obvious name for this new table, they are all “subjects” that the student is or could take.
The New Table So now you have a name for the new table, “Subjects” and to link it to the data remaining in the original table, (first name,- last name) it will need to have a field which contains a match to the RecordUniqueID field. For this example let’s call this “MatchingID” then you need a field to record the subject and another field to record whether it is true or false. For the purposes of this demonstration I have terms these “TransposedSubject” and “TransposedData” and you can see what this should look like below:
Free Normalization Tool (see attachment) How do you get that new table you may ask? Well originally it was quite a tedious task, especially if you had many columns of data to move, you had to construct an append query and append each column. However I realized it may be a process that would lend itself to automation of some sort, and I came up with a form for handling this. This is my “Beta” version, and to make it “better” I need feedback!
Feedback!!! Please be good enough to download the attached database and experiment, please tell me where it can be improved, where it's wrong and where I could do things better. Cheers Tony
Please note I have updated the attached form, Minor changes only. (10th Feb 2008 5.30pm)
Part 2 now available HERE: (http://www.access-programmers.co.uk/forums/showthread.php?t=143607)
I was importing the large array of accounting data into Access from Excel through File - Get External Data - Import. Several columns contained tangible accounting data (e.g. registration numbers) that had different formats like "23423 34", "32-3545" and in some cases even contained letter constants. Therefore, I have assigned TEXT type to this column while importing and was sure that all values are going to be stored as text. However, what has happened is: 18307769 turned into 1.83078e+007! this means tangible accounting data that was used as a key field for further linking turned into bull&&$%! :-(
I know this problem is very simple, so please, can you give me a hint as to how do I solve it in the future.
Thank you in advance.
-- Regards, vb707 securely sell goods and services on-line (http://www.securemarket.org)
Is there a way to open an Excel file in the background, run an Excel macro to format the data as I need it, then import/append data into my Access table all with one button click?
Very new to access, having a little trouble :/ trying to combine 2 excel sheets in a Access DB.
I am able to import each sheet individually but I cant seem to get them to combine.
In each sheet their is 7 columns, 300 rows, with one similar column "Serial_Number" per sheet so what I wanted to do was combine both Excel sheets by using the S/N column, and keeping both sets of information intact.
For instance if the 1st excel sheet was the following. ¦ 1 ¦ 2 ¦ 3 ¦ Serial Number ¦ 4 ¦ 5 ¦ 6 ¦
And the 2nd was ¦ A ¦ B ¦ C ¦ D ¦ E ¦ Serial Number ¦ F ¦
I would like to combine them to equal in any order ¦Serial Number ¦ 1 ¦ 2 ¦ 3 ¦ ¦ A ¦ B ¦ C ¦ D ¦ E ¦ 4 ¦ 5 ¦ 6 ¦
Is this possible? I will have to eventually do this to a couple of Excel sheets so it would be handy if the process was repeatable.
i have a form in my database that on a buttons on click event it creates a excel spreadsheet. my question is there a way that when the spreadsheet is created instead of over writing the data already created in my spreadsheet that i would be able to to just add to it.
This might have been asked before but I just dont seem to find one!!
I need to retreive data from 15 differnt tables every moring for various reasons. I am looking for a query that selects these records and writes it to a excel spread sheet using vba codes probably on a single click.
I have asked a similar question before but not got a very clear response.
I had hoped to be able to just cut and paste Excel macro code into an Access module and then make a reference to Excel and run the code but it doesn't seem that simple.
I have an Access Form that allows the user to enter a name (Text Box)(Owner name) they then hit a submit button which then querys a table and returns the records in a List Box below. I can then hit another button and Preview a report of the results, print the report etc. I have to take this one step further and create a way to print these returns on envelopes, mailing labels, and certificates. I tried this in Access and I cannot get information to line up correctly. ( I have specific layouts I need).
What I do have is an Excel spread sheet that has macros in it. I was thinking that If I could export those returned records from Access into Excel (The Spreadsheet with the MACROS) then I could just run the Macros in that Spreadsheet.
QUESTION: Does anyone know how I can open a specific Excel Spreadsheet and import the records from my Access Query (The ones that show up in the List Box)
