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.
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=<
Does anyone know how to change all linked tables in a database to unlinked? I want to save a database for offline testing and want to save the tables as flat tables instead of linked. Is there anyway to do this without bringing them all back in unlinked?
This is one of this only times i've had a problem whose answer i couldn't find on this or other forums, so here's the question.
My DBase links to the back end tables tblBrkdn tblBrkdnArchive tblBrkdwnTradespeople tblBrkdwnTradespeopleArchive
The following two tables are local, not linked tblBrkdnArchiveTemp tblBrkdwnTradespeopleArchiveTemp
After running through the following code, all of the four above mentioned linked tables are now local. It's vexing. I'm terribly vexed. If you can help, my status would chnge from vexed to joyous....:
Thx in advance...
' 1) Join all together (Each Table) ' Create two recordsets, One for the Brkdn set, one for the Tradespeople Set. ' Brkdn: Want it to be the Union of all records in (tblBrkdn, tblBrkdnArchive) without duplicating BrkdwnID 'Standard Union Query with no duplicates. Transferring data into a temporary table SQLStr = "SELECT * INTO tblBrkdnArchiveTemp FROM (" SQLStr = SQLStr & "SELECT tblBrkdn.* FROM tblBrkdn INNER JOIN tblBrkdnArchive ON tblBrkdn.BrkdwnID = tblBrkdnArchive.BrkdwnID " SQLStr = SQLStr & "Union ALL " SQLStr = SQLStr & "SELECT tblBrkdn.* " SQLStr = SQLStr & "FROM tblBrkdn LEFT JOIN tblBrkdnArchive ON tblBrkdn.BrkdwnID = tblBrkdnArchive.BrkdwnID " SQLStr = SQLStr & "WHERE (((tblBrkdnArchive.BrkdwnID) Is Null))" SQLStr = SQLStr & "UNION ALL SELECT tblBrkdnArchive.* " SQLStr = SQLStr & "FROM tblBrkdn RIGHT JOIN tblBrkdnArchive ON tblBrkdn.BrkdwnID = tblBrkdnArchive.BrkdwnID " SQLStr = SQLStr & "WHERE tblBrkdn.BrkdwnID is null);" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
' BrkdnTradespeople: Want it to be the Union of all records in (tblBrkdwnTradespeople, tblBrkdwnTradespeopleArchive) without duplicating ID 'Standard Union Query with no duplicates. Transferring data into a temporary table SQLStr = "SELECT * INTO tblBrkdwnTradespeopleArchiveTemp FROM (" SQLStr = SQLStr & "SELECT tblBrkdwnTradespeople.* FROM tblBrkdwnTradespeople INNER JOIN tblBrkdwnTradespeopleArchive ON tblBrkdwnTradespeople.ID = tblBrkdwnTradespeopleArchive.ID " SQLStr = SQLStr & "Union ALL " SQLStr = SQLStr & "SELECT tblBrkdwnTradespeople.* " SQLStr = SQLStr & "FROM tblBrkdwnTradespeople LEFT JOIN tblBrkdwnTradespeopleArchive ON tblBrkdwnTradespeople.ID = tblBrkdwnTradespeopleArchive.ID " SQLStr = SQLStr & "WHERE (((tblBrkdwnTradespeopleArchive.ID) Is Null))" SQLStr = SQLStr & "UNION ALL SELECT tblBrkdwnTradespeopleArchive.* " SQLStr = SQLStr & "FROM tblBrkdwnTradespeople RIGHT JOIN tblBrkdwnTradespeopleArchive ON tblBrkdwnTradespeople.ID = tblBrkdwnTradespeopleArchive.ID " SQLStr = SQLStr & "WHERE tblBrkdwnTradespeople.ID is null);" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
'delete everything from the four non-temporary tables 'tblBrkdn SQLStr = "DELETE * FROM tblBrkdn" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
' 2) Paste the entire record set to each Brkdn table 'tblBrkdn SQLStr = "SELECT * INTO tblBrkdn FROM tblBrkdnArchiveTemp" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
'tblBrkdnArchive SQLStr = "SELECT * INTO tblBrkdnArchive FROM tblBrkdnArchiveTemp" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
' 3) Delete from Active & Archive Brkdn tables WHERE [DATE] < txtArchiveDate.Text 'tblBrkdn SQLStr = "DELETE * FROM tblBrkdn WHERE ([DATE]<#" & dateStr & "#);" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
'tblBrkdnArchive SQLStr = "DELETE * FROM tblBrkdnArchive WHERE ([DATE]>=#" & dateStr & "#);" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
' 4) Paste to Active Tradespeople table WHERE Associated Breakdown Record [DATE] >= txtArchiveBeforeDate.text SQLStr = "SELECT tblBrkdwnTradespeopleArchiveTemp.* INTO tblBrkdwnTradespeople FROM tblBrkdwnTradespeopleArchiveTemp INNER JOIN tblBrkdn ON (tblBrkdwnTradespeopleArchiveTemp.BrkdwnID=tblBrkd n.BrkdwnID)" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
' 5) Paste to Archive Tradespeople table WHERE Associated Breakdown Record [DATE] < txtArchiveBeforeDate.text SQLStr = "SELECT tblBrkdwnTradespeopleArchiveTemp.* INTO tblBrkdwnTradespeopleArchive FROM tblBrkdwnTradespeopleArchiveTemp INNER JOIN tblBrkdnArchive ON (tblBrkdwnTradespeopleArchiveTemp.BrkdwnID=tblBrkd nArchive.BrkdwnID)" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True ' 6) Clean up: Delete all records from the temporary tables 'tblBrkdnArchiveTemp SQLStr = "DELETE * FROM tblBrkdnArchiveTemp;" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
I need to update the names of my ODBC linked tables in my Access database, how can I do this without causing issues with my queries/reports?The current linked tables are to a SQL View on a database called mcsrm_live, and called e.g. vwDamagesReportNew
The new SQL views that I need to link to are identical in structure and content and on the same SQL server but different database - forkdw and are called e.g vw_R_Damages
Is there a straightforward process to do this without affecting the queries and reports in my Access db?
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 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.
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..
I have one database called asset management. It consists of one main table called cyber assets. Most fields in this table are linked to a manually created lookup table inorder to restrict user input. There are also two additional, none lookup, tables used to list a) the IP addresses (there can be more than one) and b) another similar 1 to many type table. Basically this DB is used to manage basic cyber asset data, excluding most items related to configuration management.
So, this above DB serves the purposes of asset management. Now I essentially need a similar DB for Patch Management. What I've done for this is to assess each patch initially (i.e. just by looking at the patch title and determining if we even have any of those device. i.e. this assessment is not based on OS, model number... just a general 'may' or 'may not' be applicable). Here's what this SEPARATE DB looked like:
Since each patch is essentially assessed against itself, or maybe a better way to describe it is against the users memory of what we do and don't have, only a single table and form was needed.
So now we've been thru this process and the DB is filled, all initial assessments are complete. The next step is to take all the ones that are applicable to our company (based on the initial assessment when you answer, yes is applicable) and do assessments based on each device we have.So what I want to do is to link the two DBs on a new table called Patches_by_device, inside the original patching DB... so the relationships would look like this:
But as you can see, the linked table CYBER_ASSETS has some sort of undefined relationship type, which is causing my issues.So the next thing I did was to autocreate a form based on the Patches_by_device table, and here's the result.I need to change the patch_key to the Patch_ID+Patch description+URL, etc, and to change the device key to the the UNID+IP+functional description, etc...so I changed the form record source like this:
Now I should be able to change the control source of the Patch_key and Device_key to more useful information. so I changed: Patch_key control source to Patch_ID and Device_key control source to UNID (which is in the cyber assets table)
As you can see, it worked for the patch_ID but not the UNID which is part of the linked table.Must it be within one DB, because we have a ton of other modules to implement (e.g. config management, vulnerability assessments, audit stuff, and more...) and I'd like all these to be in individual DBs, all liked back to the main cyber_assets/Asset management DB.I've considered just modifying that patch table so that each device has its own column heading in the table, but this will cause issues when new devices are added.
I am taking over a database someone else created but is no longer here. The tables are linked to an external oracle database. I know the links exist because when I run a query it prompts me for my user name and password. However, the tables look like regular tables. There is no icon showing it's a linked table so I don't know which tables are actually linked and pulling from the Oracle database.
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.
Hello Friends I have 100 tables and now I changed the name of tables due to some reasons. Now I must to replace the names in other database objects.Is there any option that can reduce the work load because otherwise it is a tiring job to replace it manually.
I have a problem with changing a datatype into a number. The thing is that all the fields are in text and i want to change some of them to a number datatype. If i try to change the field to a number i get a message that come up as This:The setting for the field size property of on or more fields has been changed to a shorter size. If data lost, validation rules may be violated as a result. I try to export it into an excel file and change all the field that i need to be a number and convert them into a number and it work by when i import them back into access they don't change.
I have a database with the tables linked from another database. The problem is that i need to copy the database to and from another machine in order to work on it. As this involves putting it in a different location each time, i have to relink the tables. Is there a way to link the tables reletively so that as long as they are in the same Directory they will be linked?
I have a frontend and a backend to my database. Actually, I have two backends....one with real data and one with sample data.
Currently when I am switching between databases, I am using the Linked Table Manager, which I am finding to be a bit of a pain. If I am already linked to one of the two backends, in order to switch to the other I have to rename the one currently in use so that it will prompt me for the new source.
I think MS Access looks for the current link before asking you for a new one. If the current link is still active it won't ask you for the new one. Renaming the current link effectively makes the link 'inactive' and allows me to link to the other database.
Does anyone know a better way to do this?? Can I change the link in code?? That way I could just make myself a form that I could use to switch between the two different backends.
Any other ways to resolve this would also be appreciated.
How can I prevent the user to link tables from SQL Server?
In SQL Server, I give out read and insert permission on specific table in SQL Server, I create one MS Access database which linked tables, I hided the database windows, create the forms. I want the user to input/read the data via the forms.
However, if the user create the new MS Access file, and then use ODBC to link tables himself if he knows server name and database name. He can see and input the data into the table directly.
So, how can I prevent the user to link tables to their files?
Hi there people. Just wondered if anyone had linked an .mdb backend to a .accdb frontend and wondered if they encountered any problems. Or indeed, if it's do-able.
Oh, the frontend is Access2007 and the backend is Access2003 The data tables are exactly the same it's just that the forms are prettier in 2007 and the records in reports can be edited individually.
Hi folks, I have a table and I want most of the fields available to others. I also want the other users to be able to add fields to the table (not the master but theirs). Here's an example to explain it better:
MasterTable contains the following fields: Name,Address,Phone,Training,Checks.
I want the Users to have a linked table with access to: Name,Address,Phone
I also want the users to be able to add their own fields like: Date of Birth etc. to have a table that is linked to Master but they can change some fields.
Any suggestion would be much appreciated. Thanks, Colin.
I am connecting linked tables in access 97 , my database is splitted into frontend and backend and i am linking tables to backend but i gets an erros application or B]object definition [/B] error. whats the way out?
I have a database that is really just a bunch of reports based off of one excel sheet. I am using the excel sheet as a linked table. My problem is that there is going to be a number of copies of this database in use, and i want the user to be able to say where this excel sheet is located, but not every time the database starts up, only when they change location.
the database is not opening up with the database window displaying so i cant teach them how to go to the properties and linked table manager.
what can i do that will detect if the database cant find the table, the linked table manager will open up automatically?
I have 7 tables to track client data. The 1st table uses AutoNumber to create the 'Client ID' number. The other 6 tables are linked to this table, and in turn each other, through the 'Client ID' field.
All information entered in all 7 tables is done so on one form. This form has a tab control to separate the input fields.
The problem is this. If I enter information in the form, but no field in one or more of the tables gets filled (table gets left completely empty because the fields were not relevant to that record), the record does not save itself to where it can be viewed in the form anymore.
I have programmed a command button to copy the 'Client ID' from the 1st table to the rest, and so far this works, but I would like to have this done automatically without any action required by the user. I tried the 'OnDirty' for the ClientID field on the form, but I do not think that works. Mybe 'OnUpdate'?
I have a text file containing approx. 45000 rows of data. I've created a linked table to upload the file into access keeping the seperator's in place. The file has date and time recorded as e.g. 20050123 for 23/01/2005 and 91527 for 09:15:27.
Therefore I'm trying to run a macro on the linked table to delete all records from an existing backup table, append new records to it, convert dates, convert times.
However, every time I change the text file to another set of data, the append query falls over. If I go into design view of the query, delete the fields and recreate the fields the query will then run.
It appears as though because the data being linked has changed, the query doesn't recognise the linked table anymore.
