myDatabase has a load of linked tables from myTables. Problem; my network drives are mapped differently from the users, so the links do not work for me.
So - does anyone know how I can set up a relative link, so myDatabase looks for datamyTables? i.e. it looks from the source directory rather than checking the whole path back to the root?
I have a PATIENT TABLE with PAT_ID Autonumber / Primary Key.I want to set up an Invoice and a Receipt. I know the fields I need. how many tables I should create and how they should be linked PROPERLY.
INVOICE TABLE would contain (field names and data type): Invoice_ID Autonumber / Primary Key PAT_ID Number (as the link to the Patient Table) Invoice_Date Date/Time Invoice_ Contact Text Invoice_SentDate Date/Time Invoice_Paid Date/Time Invoice_Comments Memo (not to be printed on invoice just info for me)
[code]...
Note: The rest of the fields needed on the receipt are in the Patient table (i.e. services rendered to). For the receipt, the description of the service(s) will be limited to a standard text that will be set in a label on the report.
I have recently learned how to link photos to a database using a form and a table listing the path to each photo. Is there anyway to format the records in the table so that when you click on the path the photo shows up?
I have browsed around past threads and have not found and answer.
I have a query where I am trying to set the where criteria expression using an unlinked table. The unlinked table name is INV DATES. The criteria field is [THE_DATE]. I cannot really link the fields for the two tables since I am using an expression. This design works, but it goes really slow.
Any ideas?
SELECT DISTINCT DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER, DSSAPP_FULL_WIP_PROD_STATS.OPERATION, DSSAPP_FULL_WIP_PROD_STATS.OPERATION_START_TIME, DSSAPP_FULL_WIP_PROD_STATS.OPERATION_END_TIME, DSSAPP_FULL_WIP_PROD_STATS.CURRENT_QUANTITY AS QUANTITY, DSSAPP_FULL_WIP_PROD_STATS.CURRENT_QUANTITY_TIME AS [DATE], DSSAPP_FULL_WIP_PROD_STATS.LOT_HOLD_FLAG, DSSAPP_FULL_WIP_PROD_STATS.ACTIVE_FLAG, [INV DATES].THE_DATE, [INV DATES].WEEK_NUMBER_IN_YEAR, [INV DATES].MONTH_NUMBER_IN_YEAR INTO ROXES FROM DSSAPP_FULL_WIP_PROD_STATS, [INV DATES] WHERE (((DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "R1*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "R2*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "R4*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "R5*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "RR" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B0*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B1*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B2*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B5*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B4*") AND ((DSSAPP_FULL_WIP_PROD_STATS.OPERATION_START_TIME)<=[INV DATES]![THE_DATE]) AND ((DSSAPP_FULL_WIP_PROD_STATS.OPERATION_END_TIME)>[INV DATES]![THE_DATE] Or (DSSAPP_FULL_WIP_PROD_STATS.OPERATION_END_TIME) Is Null) AND ((DSSAPP_FULL_WIP_PROD_STATS.CURRENT_QUANTITY_TIME )>=Now()-600) AND ((DSSAPP_FULL_WIP_PROD_STATS.FACILITY)="STPPRD" Or (DSSAPP_FULL_WIP_PROD_STATS.FACILITY)="EPIPRD")) ORDER BY DSSAPP_FULL_WIP_PROD_STATS.OPERATION;
As i'm still what can be called a "newbie" in MS Access, maybe one of you wizards can help me out with this.
I'm having a product table in ms access, containing a few columns. The regular stuff (type, ID, ..). It also contains a column i want to store the links to their documentation to, called "download'.
The fields of that table are loaded into a data access page.
Now the data access page is loaded into the frame of an HTML page. Everything works well, but when it comes to the download-field, i get no link, but a piece of code instead.
Now these links are relative links (not absolute ones). What i'm searching for (for quiete some time now), is a way to make the links show as normal links.
Is there anyone who could give me a hint or who can help me out with this?
I was recently doing a little Spring Cleaning in my database and deleting old tables and queries that were no longer used or had been for experimental purposes. Unfortunately, I unknowingly deleted a query that was being used by a report I still needed and it took me several hours to find and correct the problem.
Not wanting to repeat this mistake, I began searching for a way to display my reports, queries, and tables and all the objects each is linked to. I thought it would be nice, for instance, to have a hard copy list of all my queries that showed where they are derived from and what other objects reference them. Is there a way to do this?
I've tried using the "Relationships" button and working with the Documenter, but I haven't found what I'm looking for. I have also tried a Google search, but, admittedly, I may not be using the proper terminology to get good results.
Once external tables have been linked to a database can the links ever get dropped accidentally.
ie should I test for missing links and recreate them programatically or assume that it will never happen?
It is easy enough to identify links which are present but any code which loops through the tabledefs will only identify links which are present.
I think the best approach, if this is necessary, would be to count the number of external links, compare the answer to what it should be, and refresh all the links if theree is a difference.
I have an application in MS Access that contains some forms and reports. I want to be able to show pictures on the forms. So I am saving images on file system and storing links to those images in MS Access tables so that they can be retrieved and be shown on the forms. Can someone throw light on the effects of this approach on performance of the application (especially vis-a-vis the approach of storing images in the Access tables as OLE objects)?
Would appreciate if responses can be directed to my email-id Satya_Nadiminti@infosys.com
I have a club member registration application consisting of a program database and a data database. They reside in the same directory. There is an autoexec macro which runs at the beginning. This macro has to find the DATAdb and make sure that the required tables are linked (Not all tables). If it cannot find the DATAdb and then has to make a call to the user to use a different procedure.
Now to find the DATAdb I have used the following code that works. The function GetPathAndName parses the string db.name and outputs the path and name of the PROGdb. Up to here no problems.
Code: GetPathAndName db.Name, FrontPath, dName
Then I use the following code to get the fullpath of the last database used and thereby find the path and name of the last DATAdb. The path of PROGdb is used in place of the existing path of DATAdb to check whether the file exists.
Code: Dim rs As Recordset, cPath As String, cName As String Set rs = CurrentDb.OpenRecordset("SELECT Database, Type " & _ "FROM MSysObjects " & _ "WHERE ((MsysObjects.Type) = 6) AND ((MsysObjects.Name) = 'MembersTbl') " & _ "ORDER BY MsysObjects.DateUpdate DESC;")
[Code] .....
As the final step I use the table definitions to refresh links that already exist and connect tables that are not linked The code is below. But I can not get it to work. It links 7 tables instead of 16 and when tested in different folder says invalid operation and so on. There is fundamental error some place but I could not figure it out.
Code: On Error GoTo Error_Linking Debug.Print "TableDefs.Count="; db.TableDefs.Count For I = 0 To db.TableDefs.Count - 1 Debug.Print "TableDefs("; I; ").Connect="; db.TableDefs(I).Connect Debug.Print "TableDefs("; I; ").Name="; db.TableDefs(I).Name
I searched and couldn't find anything on this specific issue.
I'm on the verge of designing a pretty big database and I've run into what may become a bit of a problem. There will probably be several backends in different locations. Should another backend ever be created or if the location for one changes, the user can use a form to refresh the links to the tables on that specific database.
The way I've done this so far is by having a piece of code that checks the tables for a connection string. If the table has a connection string, its a linked table, so the link gets deleted and replaced with the new link. Well this will no long work since there will be several backends and only 1 of them may need its tables relinked and using that code will delete all the table links, including the good ones.
Is there a way to make 'DoCmd.TransferDatabase' overwrite table names instead of giving the duplicate tables a number suffix? I think that doing this would be easier than retrieving the table names from the new backend and comparing them with the linked table names in the frontend so the old links could then be deleted.
I found 2 examples of code to refresh my attached table link to sharepoint lists.
I have a scheduled task open my Access 2010 db and an autoexec macro runs and closes the db.
My problem is my tables disconnect from sharepoint 2010 and the update fails.
I added the code I found to a module and added a line in my macro to run the code. Now I cannot get the code to work.
Am I at least on the right track? I just want to know before I spend many hours getting this code to work.
These are the two locations of code I found.
[URL]
I think part of my problem is that I have a regular table that I add a txt file to that is not linked to sharepoint so I need to exclude it from the code.
Is there a way to just select like through a find dialog box, the location of a databases backend location and just automatically create all the links for the link tables...
The reason Im asking, is my boss wants the database to be on his laptop in the event that the network is down, and also so that he can work away from the company's location but I was showing him the implications of having it installed locally on his machine should his laptop go down....
So now I'm wondering if i could
1. do some automation that allows him to just choose the location of the databases backend that he would be working from, be it on the network or on his machine
and
2. using both backends to update or overwrite each other depending on which he was working from or did updates to..
Really would love all the help and input I can get on this... Im willing to work on developing a sample for this if nothing is exactly already out there so that it can be reposted cause i think this would be very useful to everyone's database applications.
I have a client who needs a Database created in Access for him. he would like to regularly auto-populate this Access DB from an Excel file - like twice a week.In addition, he wants me to create Forms, Queries, reports and Macros for him in the Access, which will have 2 Tables from Excel files and about 3 Tables created in Access.I figured that I could do that if I gave him a link to the Tables he needed from the Excel file(s). But how do I work remotely for him- the path to those files is on his computer. I can load them to mine, but then he can't open the files.How can I create Forms etc. in an Access DB which uses links from source files as its Tables?
I have a database with a number of linked tables that are linked to tables in different databases (not a back-end).for example, I have table1 that is linked to table1 in K:databasedb1.mdb.table2 linked to table2 in S:datadata.mdb.and so on...
However, recently we have moved all our databases to a new location.
K:databasedb1.mdb is now residing in O:masterdatabase and S:datadata.mdb is now residing in O:masterdata and so on...
I'm now in charge of relinking all those tables to point to the new location.I would do this in linked table manager one by one but we have 100s of tables linked to multiple different databases in different location.is there a way to create a VBA code that will automatically do this re-linking process?
so, 1. find unlinkable tables 2. search its new location under O:master 3. re-link it to the new location
Database names and tables names have not been changed. Just the location of databases.
I am trying to link an images (.png) onto a form. I have a folder of all the images I want but I want the path to be relative. So that if I move the access file and the images folder to a new location, it will still work.
In the properties for the image, It works fine as C:UsersjohnDesktopimage1.png but what I am looking for is the ability to use "imagesimage1.png" as the file will always be in that relative folder to the db.
What is the correct syntax for this? putting image1.png in the same directory and just using image1.png works, but this won't be very tidy with 100+ images.
I know from searching it is possible through VBA by getting the root directory of the folder and appending the filename. Don't really want to go this root. I also don't particularly want to create a table to store the images or links.
I have a large database. I want to identify which objects take up the most space. Any way of identifying a list of each object with their relative size?
I have developed a Microsoft Access 2010 database for my client and the database is split with Front-end/Back-end, the Back-end and the database is shared on Network, The client operating system and applications for all users are hosted and consistent and the service is delivered over Citrix.
The database some times corrupt the tables record and give a permanent #Delete Error, I have attached one of the database table and the screenshot of the error,
I have split database (B/E is in the SharePoint library, F/E has users on a local PCs). Sometimes, when I update/add data (does not matter if it is via form or directly in the table) it looks OK, but when I re-open the database, the data are gone.
Problem is that I cannot catch the moment when data were not saved (sometimes data are saved, sometimes not). I can point out this: if I re-enter the missing data, primary key continues subsequently, it looks like the data have never been entered. I tried to use script
Code: If Me.Dirty Then Me.Dirty = False
on "On Close" form event, does not work.
B/E is linked by VBA code and it looks OK (no error, Link Manager shows correct path). I suspect interrupted connetion to the SharePoint but I don't know how to check it. I implemented VBA script co keep open connection to the SP but the issue persists.
I have linked tables from SQL Server using ODBC connection that their location never changes. I have used certain fields of those tables to create queries and make table queries to derive to the information I needed.
On these tables on SQL Server, there is new data added daily. Every day, midnight, there's new data records added of whatever transactions took place in that working day. how often do I need to refresh linked tables in this case to get the latest data added. I mean, once I am linked, the make table query using those defined fields, would it get the latest data added by default when the query is executed, or I must refresh linked tables using Linked Table Manager and then run make table query.
Also, if I want the access to automatically refresh linked tables, can I use the following code? I have added this code, and executing it through a button, but I don't see anything happen, the database becomes inactive for couple seconds (I guess while it is updating) but I don't know is it updating the tables for sure or not, though I am not receiving any error when executing the code through the button.
Function RefreshLinkedTables() Dim tdf As TableDef For Each tdf In CurrentDb.TableDefs If Len(tdf.Connect) > 0 Then tdf.RefreshLink End If Next td End Function
I have linked tables in my db at the moment that rely on user dsn connections to an SQL server. I've been reading about DSNLess connections and want to try convert what i have to have permanent DSNless connections, but the code I've found doesn't appear to be working.
I've removed server specific details where i felt necessary, but when running the code i have it in place.
Code:
Public Sub RefreshODBCLinks() Dim connString As String Dim db As DAO.Database Dim tb As DAO.TableDef connString = "DRIVER=SQL Server;SERVER=<database ip address>;DATABASE=<
I have a split database.(Access 2010).Three of the linked tables are Appointments, Appointments_OLD, Appointments_NEW.
I want to use vba to rename Appointments as Appointments_OLD (replacing the current one) and to rename Appointments_NEW as Appointments (replacing the current one)
I have used:
'replace Appointments_OLD by Appointments, replace Appointments by Appointments_NEW DoCmd.Rename "Appointments_OLD", acTable, "Appointments" DoCmd.CopyObject , "Appointments", acTable, , "Appointments_NEW"
Unfortunately this just made Appointments and Appointments_NEW clones of one another - changes in one automatically occur in the other.
What I actually want to do is to swap the names round in the backend database while maintaining the right links to the frontend.
I have a database that I migrate to different locations as we change stations (military). I keep the master on my C: drive and then put a copy onto the shared network drive. I keep my images in a subfolder of the master db.
example:
C:Pirate (master db) C:Piratedbfiles (where I store the images)
I have the images "linked" on the forms to the subfolder "dbfiles" that is on the shared drive. However, as we change locations the drive letter changes for the shared drive. I want to make a "relative" link to the pictures in the properties box as so:
Piratedbfilesimage1.jpg or dbfilesimage1.jpg
However, it keeps putting the C: in front of the text or else it won't find the images. I desire it to look in the subfolder of the active db (whether or not it is on the LAN or hard drive).
I know I can embed the images, but that exponentially increases the size of the db. I've played around with putting them in a table, but couldn't make that work either.
Hello All, I currently have a bunch of tables that I have linked to a SQL database. For development purposes (easy of use), I'd like to turn all those tables into regular tables rather than linked so if I take the db offsite, all the data is with me. I realize there will be no updates, etc. but that really doesn't matter for my needs in this case.
Is there a script or an easy way to do this? (besides doing a manual import for a LOT of tables)
I've tried searching but I might not be searching for the correct terminology.
looking for a way to export the list of table names, table types & if they are linked (e.g. tbl Sales Linked .dbf or tbl Staff linked to excel) from a database - this has to be done for about 300 databases.
in an individual db, I have a make table query off of the table MSysObjects to get the data. The Database field tells me where the source of the linked table resides & the ForeignName field gives me an idea of the format of the data source (e.g. dbf or excel). I could manually import that query into each db, run it to get the table names, then copy & paste..
