Having experimented with Access for a few months now; I know you can link an Access Database to SharePoint, and you can export Excel files into Access for inclusion in tables.
My question is; If I have a daily report being produced, through script, with an excel output. Is there anyway to automatically publish this in Access, negating the need to manually go into Access to import?
Hi all,
It's my 1st posting! Need help on how to get Unbound OLE objects (i.e. lines, pics, et.) reflected when publishing my Access report into MS Word. So far, only the data are exported. Appreciate your help.
System Info: Intel Celeron 700MHz, W98SE, MS Access 2002
I need some help on this one. :confused: I have two Crystal Report generated Excel spreadsheets that are auto-updated on a daily basis. One spreadsheet contains computer assets, type, model, locations, etc. The second spreadsheet has users assigned to them, phone numbers, etc. I need to import these in to existing tables with numerous filters into my Access database weekly. They don’t change a whole lot but I need the changes to be reflected in my database.
The way I have it setup now is through a linked table, then I use a “Make table query” to filter the data.
My problem is the filtered table has relationships set up that I have to delete then recreate every time I need to run the “Make table query” because it has to delete the old table first.
Is there a better way to make this happen?
what I have in Access is a table which lists jobs via their HB Number (as well as a ID number access gives them). For each job there is a bunch of details and some Yes/No fields.Each day I will get a dump from another system that will list jobs like this, I then need to "Update" the Access table with any new information from the excel dump. The update would need to:
1. Insert new records (jobs) from Excel dump into Access Table
2. Update any of the records fields (except HB of course as its unique) in the access table from the Excel dump (the Excel dump obviously wont have access ID numbers, but will have the HB)For a real basic example:
Access Table Like So:
ID
HB
Finished
1
5A
Yes
2
5B
No
[code]...
the actually data has lots of different fields and many many many more records. But yeah basically need to update the table from an excel dump.I thought it was as simple as doing a Excel Import > Append Table thing in Access, but that just seems to add the new records and ignore the updated fields?
Hello,
I am not sure how complicated of a job this is, but we want to be able to import our daily recievals of inventory into our existing inventory database instead of entering it by hand. I try using the wizard, however when I get to "finish" it says there was an error and it was not imported.
I don't know much about access but I was hoping one of you could point me in the right direction.
I am working on Access2007. I designed a report that I need to email every night automatically to my client. The report resides in the Access database. I currently use a macro to auto email this report for me but it still requires me to double click the macro to make it run. I actually want the report to be email at night without having me running the macro manually. Is there any way to make this happen?
View 7 Replies View RelatedHi,
I was wondering if there was a way for a report to be automatically saved as an excel file. Also, the Access database is stored on the company's server so would you be able to choose the path where the excel file would be stored?
Cheers,
Ben
Recently I set up a button to export a report automatically to Excel using the following code: DoCmd.outputTo acOutput Report, "Report1", acFormat.xls, "J:Insurance DeptReport1.xls", True.This works great, however I was wondering 2 things:
1. How do you set up to transfer multiple reports using the same button. EG I have reports named Report2, Report3, etc how do I add these to the code to export as well?
2. Is it possible to have them transfer to the one file but different sheets. EG Report1 would go to sheet1, Report2 to sheet 2, report3 to sheet 3 and so on?
Is there a way to link a ACC table to an Excel spreadsheet so that when changes are made to one it automatically changes the other?
And -
How do I link (or join) two (or 3) Access tables so that the above happens - when a change is made to one it's made to the other.
Example:
I have an overall big table with 7 fields.
I created a query from it that has 5 fields. Or create another table that has 5 fields.
Can I join/link them so that changes made to one is made to the other?
Example:
An ID number changes
or a wage salary changes.
Thanks in advance.
I have an excel spreadsheet that i have linked to an Access database. I need to append and delete records from the spreadsheet into an existing table in my Access database. I am able to append specific records from the spreadsheet into my table but then i want to delete the records from the spreadsheet. When i run the delete query i get the following message; "Deleting data in a linked table is not supported by this ISAM" Is there some way i could get this to work?
I have records in this spreadsheet that needs to get into the database but once it is appended to the database i need those records deleted from the spreadsheet. I am kinda using this spreadsheet as a temporary table so one employee who does not use access can put data into his spreadsheet and then the other employees can pull want they need from this into the database.
Any suggestions?
I have a table which is being populated with data when I click a button in a form. Now I want the data of the table to be shown in an excel file but the problem is that I want that data to be refreshed everytime I insert new data in the table.
Any ideas??
I'm trying to use a template to track assets-specifically this template:URL....Is there a way I can link an excel doc to my database that provides all of my data, that is thereby linked to other tables within Access?I want my data to be updated as it is manipulated within the independent excel doc when I open access.
View 6 Replies View RelatedI have a number of Excel spreadsheets (data sources) hosted on Sharepoint that I need to link to one single Access database.
At first. I cannot link the spreadsheets directly to Access because Access cannot read the spreadsheets on SharePoint to make the links.
Secondly, I can export the spreadsheets from SharePoint to a local folder on my PC and keep them 'refreshed'. The problem with this is that I cannot link Access to the local refreshed spreadsheets because of the (auto) extention .iqy. Access does not know the extention .iqy and hence the two cannot be linked at all.
Thus: How do I link an Excel spreadsheet hosted on SharePoint to MS Access.
I am trying to link an excel file (.xlsb) into Access 2013 running on Windows 8 (External Data-->Excel). It will first act like it's working, but it will just shut Access down after a couple seconds of churning and ask for a Backup to be created or it will restart (no rhyme or reason as to which you will get). I've tried a Compact and Repair. I've tried rebooting. I've tried renaming the excel file(although I have other files with the same naming convention linked), but no matter what I do, it will not accept it. I've tried doing just a straight Import....same results. This file, along with a others that are already linked in this database, are stored on a network server.
View 7 Replies View RelatedHi,
just starting out building a database product for mineral collectors and wanted to know what to do went it is complete.
If you want to issue the finished collectors data base as an application to other users do you need a program generator?
I have heard o the Access tollbox, does this do carry out the generation?
Thanks
I'm working with an A/P department that is using "legacy technology" and has no capability for maintaining notes on invoices due, etc. I'd like to give them a database that would provide them with the features they need, but I'm running into a conceptual snag.
I am able to extract from their database system all open invoices each day. For each invoice, the A/P person will make notes from day to day on approvals to pay, any problems with the invoice or related inventory or service, discussions with the vendor, etc. So, let's say on Monday they make notes on a particular open invoice. On Tuesday, they'll pull all open invoices from their system and import that set of invoices into the Access database. Some will be new, but many will already be in the table. Using the Vendor Number and Invoice Number together as the table key, I can avoid duplication into the table. But, some already in the database will not appear in the new extract because they've been paid.
So, my question is... how do I get to a point where they can do this import each day and see only the invoices that are currently open AND maintain their previously-entered notes? I thought first that there would be a query to run each day, but I don't know how to keep consistency regarding table names in the query, etc, so the query doesn't have to be edited for each new import. Would this be an append to get the new ones? If so, how do I drop the ones that are not in the newest extracted data?
A complication, of course, is that I want them to be autonomous and able to do this without my input each day.
I want to publish my access database on my local pc and was thinking of using something like Dreamweaver to do this. Is this possible? I don't want to put this on the webserver but on my local machine (only a few people will be connecting), and I've read something about a Personal Web Server so this could be done. Does anyone have any ideas / thoughts on the best way to do this? Oh, I want the database / web to be "live" so the tables get updated as they are changed over the web.
View 3 Replies View RelatedI am trying to transfer daily data that I get from three different queries all into one Excel sheet. I take it that you have to make one over-arching query which I have made called Awaiting Base.
View 4 Replies View RelatedI have small program in access database which contains about 100 cca tables, querys, forms, macros in total. Customer which both program from me, wants me to enable him to access this program not just from work, it wants to access it from home or when he is on business trip from hotel etc.
I am little confused how to achieve this and what are my options? I assume I have to set by database somewhere on server and so my customer could access it any time? Do I have to upsize access database to SQL express or similar?
Also, one more important question: is it possible to "convert" access database into HTML code or similar so it could be accessed via web browser without need to install microsoft access and with freedom that it can be opened from any type of PC OS (Linux, Mac etc)?
My office recently purchased Microsoft Access for our secretary to keep track of our contacts. No other employee owns Access so we have to distribute the tables through Access Runtime. However, there seems to be a problem.
I save and publish a database using the Packaging Wizard. I then send the associated folder with the install program to a computer which has Access Runtime installed on it. But, when I install the file I created with Packaging Wizard it just opens an Access window that is completely blank. It's actually the same thing that occurs when I try to open an Access file on a computer that doesn't have Access or Runtime installed on it at all.
I have developed a database using Access 2010. This is split into a Front End and a Back End.
In the same folder as the Back End I store a number of user modifiable files in either bitmap (.bmp) or MS Word (.doc to retain compatibility with older versions).
Unfortunately, the drive letter where the back end resides will change from location to location and I can't do anything about that.
I have set up a function that returns the location of the folder where the docs reside irrespective of the back end drive location, and this works perfectly with OLE linked pictures but not with OLE linked Word documents. User changes show correctly in Word document changes at locations with the original drive assignment but other locations show only the original doc contents even though they have been changed on the local back end location. - even if I delete the Source Item info on the report!!
An example of the code I am using for the Source Item is <GetBackEndPath() & "Footer.doc"> without the angled brackets of course, where GetBackEndPath() is a global variable storing the location of the documents folder ending with a back slash.
I have tried the above in the reports Source Doc property but Access won't accept this and says it isn't valid for this property.
I need to generate a report with records generated on a daily basis and I am looking how to write the query expression. For example, my Access database generates record on a daily basis and I an using a query to generate the report. I only want to report the records created on that day, and then each subsequent day. So, in the query, I am looking for the correct expression to grab only those records generated that day. (Hope its not confusing, I'm new at this)
Thanks for any help in advance!
Keith
In our company, passwords expire every 90 days (NO EXCEPTIONS) for DB2. This means that all my linked tables can't be accessed. There are many other users using the DB's and when I have to reset my password, I get locked out a lot because I can't seem to re-link the tables fast enough. It also seems that "refreshing" the tables from the Linked Table Manager doesn't work very well.
Is there an easier way??????
The multi-user application I'm making uses data from another system. I get reports in csv format e-mailed to me in zip format. Since it's a multi-user system, I have to create a table with the records from the csv I get an error message that someone is viewing the data if I try viewing it while someone is viewing it.
When I get the e-mail with the latest version of the csv, I unzip it and replace the older file in the folder location that the Access database is linked to. I have a sub routine that deletes everything from the table and inserts the records from the latest csv. I execute the sub by pressing a button.
Is there a way to automate unzipping the file, extracting it to the folder and running the sql code in VBA?
I have a kids club which do lots of functions for kids, to monitor the members and who is in and out I did a small access data base with details of Start date of the function and end date for each member.
I want to make a daily report showing only the active members in today date only for example:
Jack have swimming every Saturday, Monday and Wednesday Between 14 July 2015 till 14 August 2015
Jill have swimming every Sunday, Tuesday and Thursday Between 14 July 2015 till 14 August 2015
today is 11 August 2015 and I want to report to mention just who is active today only.
Don't know where this question will rank. Completely weird or completely common.
I've been getting great suggestions from this forum, one of them was to begin creating seperate tables for our data (I'm currently cleaning up a mess created 10 years ago for a nonprofit). The best way I have found to link/associate/whatever tables is to use their STUDENT identification number (since this value never changes). So we linked tests to that number (many tests to each student) by entering each new test as a seperate item with a manually entered stu id. We linked TUTOR and so forth the same way. But now I would like to start linking some more things. Particularly INSTRUCTION HOURS.
The question I have is this. How do I automatically create a value in the HOURS table that links to the value (stu id) in the STUDENT table. So that every student (previous and new) will have ONE associated HOURS set each time new student data is entered.