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.
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 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'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
I need to know how to change the Access variable names programmatically.
I have a large Access database, with hundreds of variables that need to be shortened to 8 characters since they are to be imported into a SIR database, which does not allow variable names longer than 8 characters.
Because the variables have to be unique and yet still identifiable, I have to write code that will do abreviations. For instance:
I have a database that I used as a back-end for an Excel spreadsheet. Now that the database has been moved from one place on the network to another, I need to update the information in my spreadsheet to reflect the database's new name and location. Is there any way of doing this short of deleting and recreating the queries in Excel?
In my database, my "switchboard" consists of two listboxes: "Available Forms" and "Available Reports". The Available Forms listbox lists all the forms that can be accessed, and Available Forms listbox lists all the reports that can be accessed. I did this so that I wouldn't have to create buttons for each new form or report. They all are automatically listed in the listbox for the user to double-click on to open.
Since all my form and report names are not user-friendly (ie: fmComplicatedAndUglyName, rpComplicatedAndUglyReport) I want a way for the db admin to easily assign captions for each form that the average database user would find intuitive and easy to understand (ie: "Car Maintenance" instead of fmCarMaintenance). To do this, I built a table called tbDBObjectsCaptions consisting of these fields:
dbObject_ID (PK - Long Integer) Caption (Text)
Next, I have an unbound form (fmDBObjectsCaptions) consisting of two subforms:
1) sbfDBObjectsCaptions_Forms, which lists all Forms with captions 2) sbfDBObjectsCaptions_Reports, which lists all Reports with captions.
Each subform's record source is tbDBObjectsCaptions with an Inner Join between the table and the MSysObjects table so that I can show only forms (Type field in MSysObjects = -32768) or only reports (Type field in MSysObjects = -32764). So, the record source looks like:
Code: SELECT tbDBObjectsCaptions.dbObject_ID, tbDBObjectsCaptions.Caption FROM tbDBObjectsCaptions INNER JOIN MSysObjects ON tbDBObjectsCaptions.dbObject_ID = MSysObjects.Id WHERE (((MSysObjects.Type)=-32768));
(Except the Report's subform record source Where statement would have "-32764" instead of "-32768".)Each subform also consists of a "Caption" textbox and a combobox that lists all the forms or reports in MSysObjects. The Row Source for those comboboxes are:
Code: SELECT MSysObjects.Id, MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Name) Not Like "*sbf*") AND ((MSysObjects.Type)=-32768));
(Except the Report's combobox row source Where statement would have "-32764" instead of "-32768".)My first day playing with fmDBObjectsCaptions went fine. Both subforms' comboboxes list either Reports or Forms and would easily let me choose a form or report. The subforms would record the same "Id" from the MSysObjects table into the tbDBObjectsCaptions table and each Caption I typed in was recorded into the tbDBObjectsCaptions table for each "Id" I chose. It worked just fine.
However, the next day, I noticed that the captions I assigned for forms/reports were now assigned to different forms and reports! In fact, the fmDBObjectsCaptions no longer shows any captioned reports as their Types have somehow changed from -32764 to -32768 and are therefore now displayed in the Forms subform.
Either the dbObject_ID is somehow mysteriously changing for each record in the tbDBObjectsCaptions table or the Id is somehow mysteriously changing for each record in the MSysObjects table. I don't know how or why but that's what's happening. So now, in the fmDBObjectsCaptions form, my Forms subform is displaying the wrong captions for the forms, and is also displaying records that were originally Reports (items that have captions but blank comboboxes were originally entered in the Reports subform).
I attached a copy of the database. Any different solution that allows for easy Admin'ing of displayed form/report names?
I have the following script which I use to modify all report settings, with an array so that I can easily list reports that I want to change the settings for, there could be 50+ reports.
I can use a string but I have to put str1 as string, str2 as string etc... whereas an array would be easier (if I knew how to do it).
Code: Public Sub ModifyAllReportsProperties() Dim obj As AccessObject, dbs As Object Set dbs = Application.CurrentProject Dim ReportName As String
1. I want to populate a list box with all the Tables Names in my Database using VBA.
2. I also want to create another List Box or Grid (or any thing that will show table data) that when i have select a Table Name (See above (1)) it will show all the data in that table in the List Box. Was thinking some kind of SQL using vba to populate the list box?
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 a form with several data fields on it. I also have a button on the form that allows the user to duplicate a record . The reason for this duplication is so that if there will be an additional client record for the same customer, but only one piece of data will need to be changed, it's easier to copy the record and then change the one field.
However, I am getting the following message:
"some of the field names you tried to paste don't match fieldnames on the form"
and then not all data in all fields gets duplicated.
I need to figure this out, but am going nuts with it. If anyone has an idea or two they'd care to toss my way, I would be happy.
I have developed an Access database which produces financial reports based on a date range which the user specifys, taking the data from a SQL server. He chooses his dates and then runs a macro which creates the new tables, overwriting previous tables. Numerous financial reports run on these tables so I need to keep the table names the same.
The problem is that only one user can access the DB at anytime because the new user can't delete the other users table becuase it may be based on a different date range.
I'm open to suggestions but maybe what I need to so is create tables which are based on the user id when he logs in to the Access database so that he doesn't delete someone elses table. How can I do this please!!!???
i have four oracle db'ses mydb_db_test, mydb_db_production, mydb_db_develop
within access i have linked tables from the first one; let's say: mydb_db_test.tblOne, mydb_db_test.tblTwo
What i want to do is: 1) import the same tables from the other trhee db'ses 2) rename the table names by vba code example: my_db_db_test.tblOne has to become tblOne and when i switch from db (to for example the production), the tblOne has to be renamed originally (mydb_db_test.tblOne) and the other has to be renamed (so mydb_db_production.tblOne becomes tblOne).
Now the question which you probably ask me: Why not by a connection string change...
Well, that's the problem, the linked tables are being set to readonly, and the property cannot be changed (at least, as far as i tried); so that's why i thought of this workaround. By linking all three databases, i also always have for those table the three connection strings, and by renaming them (i now do this manually) i always can pick the right connection.
I ma using a .mdb file in a program and want to get the list of all tables and all the columns of the respective tables in the .mdb file. In Oracle we use 'select * from table' for getting all the tables in the DB. But how it is done in MS-Access?
In our database, we have a table called change_log which is to be filled in whenever someone adds/removes columns from a table in the database or modifies column properties.
One of the columns in the change_log table contains the name of the table which was changed. To avoid typos and misspellings, I would like to create a dropdown which displays all table names in the database. Can this be done programmatically?
Recently I have started working for one of the company where I have to deal with one of the access file. this file has lots of tables containing many fields.
My question is
How can I get all the tables name, their fields and attributes in Microsoft Word file. I have tried opening table > design view and copy text but it doesn't work. also tries coping table and paste in in word file but it takes ages
