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 an Access DB with hundres of queries and reports, now I have to use an external DB (Oracle) and export data keeping the rest, no problem with that, I have added linked tables through odbc and works fine.
My problem is that some of the tables have field names with spaces, and Oracle doesn't admit them. Does anybody knows how to solve it without having to modify all the queries, etc?, I've been thinking about aliases or views over the linked table but I haven't found a way to create these.
Hi, I have an Access database that I would like to do a mass Table Name change. Reason is, all tables start with Data.<table name> so one table is called Data.Names and I would like it to be called just Names. I can manually do this but there are over 600 tables. Would take some time.
Please let me know if there is a script or how I can approach this. Maybe I could loop through each table name where it finds "Data." and strip that part out.
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?
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=<
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.
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 have drop down list linked to table included "agent names" , the names appeared normally in the form but not Alphabetic (A-Z) although the table was alphabetic .
I'm trying to change the table links to a password protected BE DB. I found an example online, which I adapted to my needs. When I set it up to fail to find the normal BE it seems to work as intended until it gets to the line "Tdf.RefreshLink". Then it crashes with a 3031 "Not a valid password" error. The code is:
Code: Private Sub Form_Open(Cancel As Integer) Dim Dbs As Database Dim Tdf As TableDef Dim Tdfs As TableDefs Dim NewPathname As String
[Code] ....
I can't find any references or example to relinking a password protected table.
I am after a script to change the linked table paths like the following.
It will only ever be the path that is changing not the file name and only ever linked tables
Code: for each table in tabledefs if table.path = c:Testing* then table.path = w:Testingfilename if table.path = c:Jobs* then table.path = w:Jobsfilename if table.path = c:Quotes* then table.path = w:QuotesNewfilename next table
I have a number of databases that use ODBC connections for working with linked tables from a SQL database. The source SQL data has been moved to a new server with new sa credentials. I have updated my local ODBC connections to use this new information. However, when I added numerous tables to the databases I checked the box to save the password so that it wouldn't prompt me or other users every time I wanted to use the Access database. Is there a way in Access to change this stored password? So far I haven't been able to find one.
I have a access 2007 file. with a table with a yes/no field. When I link the table to another db using lnked table manager, the yes/no display is not a checkbox (as set) but is turns into textbox.
Is it possible to have the field names in a table be set by the values in another table? The desire being that for a database used in various locations, the local variations could be changed in one table which would then propagate that change throughout all the forms, reports, tables etc.
I have an Access 10 DB that includes 299 names and other associated data relevant to these names. I have a need to drop 249 of these names that are no longer needed in the DB, and just keep the 50 names that would remain in the table.
If I am in the table is there any way to somehow "designate" or select the 50 names I want to keep and then just mass delete the other 249 in one fell swoop? If I can somehow sort the 50 names so they would appear as the first 50 names in the table, then I could simply delete all the names below.
But not sure how to make this happen. It would seem to be the simplest solution. Unless I can physically drag and drop each of the 50 names I want to keep to the top of the table, but I don't think this is possible.
My employer wants me to update and revise a form with almost a hundred controls. All of the information is stored in a single table.
Is there a way that I can (easily) sort the order of columns in the table to alphabetize them? I know how to sort and filter records, is there a way to sort the columns other than the manual click and drag solution?
Note: I know that the database I'm working on for this project is not normalized. My employer isn't concerned with normalization. I've made as many corrections to the table as possible, already, but some things just won't be fixed.
I have a risk table containing risks and risk owners (many owners for one risk), meaning that I have two risk owners columns. What I would like to do is to connect both risk owner columns (containing the name IDs) to the Names table. But Access does not allow to connect more than 1 column to another.
I have an Access database that I need to import in to a SIR database that only takes fields names of a maximum of 8 characters long.
I want to create a new database with the same field names, only abreviated, and then import the data from my original database into the Access database with the new names.
Is this possible and could someone give me explicit info on how to do it?
The last line is the problem. A field name in an ADO recordset is limited to read-only persmissions for opened (already exisiting) recordsets. link:http://www.w3schools.com/ado/prop_name.asp
Is there another way around this without using an SQL "ALTER" statement. In other words, by accessing the field's name through a number like rs(i).name instead of rs("fieldname").name?
I recieve Excel worksheets with information and then import this information into a table I have in access. I verify the data and as a last step I append the data to a table called AllReportData2012 which is used to pull information for my reports. Part of the Excel information that is imported to a new table includes the employee's name with work details in each row.
In my access report I do not use the employees name, I use their employee ID number. I have a table in my acess database that contains 2 columns. The employees Name and their associated Employee ID number.After I import the Excel information to Access how do I set up a macro or programming that will look at the name in each row and change it to the correct Employee ID. I could have anywhere from 20 rows of recods to 600 rows of records.
I am using Access 2010. I used a template called calltracker from Microsoft. I have adapted it to my use. Instead of tracking calls it is tracking follow ups. On one form there is a drop down list of reports that came with the template. I have modified the reports structure to fit my needs but in the drop down list they still show the names that came with the template. How can I change the names of the reports that show in the drop down list. How can I add additional reports I created to show in this drop down list
I have create 6 tables for library books (which are differentiated by categories). Each table has different category and unique ID name e.g. F1, F2, F3..(for table 1), G1, G2, G3..(for table 2) and so on. How do I combine all those tables into 1 table for easy search for a book rather than open up each table? Tried append query but its ID run as 1, 2, 3... , not F1, F2, F3 and it only append 1 table, not the rest.
I've a database which have some confusing names for its reports and forms. So as I found myself very confused when applying group policy to all the objects I was wondering if there is a way to rename those objects in a way that ALL the references in the hole database for the renamed objects will change too.
E.g.
Form_old_name -> form_new_name
then all the references will change both in the VBA code and in subforms references
I tried doing that mannualy and then using the search & replace tool to update the rewferences in the vba code. Turns out that all the database become a mess and now I have constant "out of memmory" messages
