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?
Thanks in advance for any help.
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:
Neglect Aphasia Conversion Baseline
Neglect Aphasia Conversion Drug
Neglect Aphasia Conversion Fluency
Neglect Aphasia Conversion Composite
will become:
N A CV BA
N A CV DR
N A CV FL
N A CV COM
Would something like this be done in a macro?
And once the variable names are abreviated, how would they be put back in the database table?
how can I use the column repmonth in the following statement?
repmonth is as a string declared and given by the user, in my case repmonth = 112013.
Code:
Public Sub TEST2()
DoCmd.RunSQL "SELECT PPC_REPORT.Reporting_Month, Report_Products.Division,
Report_Products.Product_Class, PPC_REPORT.Sales_Quantity,
Sales_QUANTITY_BP_2013.'" & repmonth & "' , Sales_QUANTITY_BP_2013.Total,
Sales_Quantity_'" & CF_PERIOD & "'.' & repmonth & "', Sales_Quantity_'" & CF_PERIOD
[Code] ......
I am using ASP to try to edit the column (field) names of my Access database. Here is an example of would I would like to work:
Code:data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ Server.MapPath("databases/logins.mdb")Set rs = Server.CreateObject("ADODB.Recordset")rs.CursorType = 2rs.LockType = 3 rs.Open "SELECT * FROM Users;" , data_sourcers(1).name="blah"
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?
Thanks.
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.
Thanks
Sherriff
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
View 9 Replies View RelatedI have an Application that I want to re-use for a second user. The only change I need to make is to re-name the fields.
Is there a tool that can do this across the tables, queries and reports for each field name change ???
Hey guys!
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
Thank you!
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?
View 2 Replies View RelatedIn 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 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 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
[Code] ....
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.
Thanks one more time, in advance!!
How can I repeat column names and row names on multiple pages of the report ?
View 1 Replies View RelatedI am creating a 2 level report to confirm an order. Main report already created, runs successfully called as subform/subreport under "OrderDetails" form. Linked to master using Order.ID. There are two versions of the confirmation report that have different layouts for different program types.
The hangup comes when I try to add a "Class Dates" subreport. It lists dates of individual classes and Skip dates. I have created the subreport as "srClassDates". When I add it to the main report, it lists the records. However, when I try to link it to the Main report, an error message box appears with the "object variable or With block variable not set".
I have tried rebuilding both the main and subreports, rebuilt the query, have not found anything that changes the result.
Linker has been working successfully on other subforms. Report with groupings works fine, but I need data from 2 tables both linked to order.id.
Error 91 - Object variable or With block variable not set
I am getting this error telling me that an object variable is not set.
I know which variable it is but when I step through the debugger it sets the variable and all is fine? Issue is that public variable of a class is not getting set when the VBA Editor is not open?
This code runs fine the FIRST time, however trows up a message the SECOND time it is run.
The error is on the line ".Range"
I am trying to sort records which have been exported to Excel.
Dim LR As Integer
LR = 5
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set wbRef = xlApp.Workbooks.Add
With wbRef
wbRef.Activate
.Worksheets("Sheet1").Activate
With ActiveSheet
.Range("A2", .Cells(LR, "O").End(xlUp)).Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlYes
End With
end With
I look at a lot of files to see when they were last updated. I wanted to write a generic procedure to manage that so ..
Code:
Public fDate As Variant
Public vField As String
Public vFile As String
'GTSdata
vField = "txt_gts_data"
[Code] ....
What I hoped Me.vField would do is update the date field [txt_gts_data] on my form with the date the file was last saved.
i.e. me. txt_gts_data = fDate
What actually happens is the variable vfield gets updated from "txt_gts_data" to 19/08/2014 then later code falls over because the fieldname is lost .
Me.[vField] corrects itself to me.vField (and does not work)
Me!vfield falls over (cannot find the field vField, not surprising J)
How do I say update the contents of the variable, not the variable itself?
Runtime error '91'
Running Access 2010.
I have two reports running off of the same crosstab query. I copied one report to make the second report, then modified the second report to change the background of column fields satisfying certain conditions. These lines of code were added to the Detail_Format section, in color below. The report with the extra code lines does not error--the original report errors.
Code:
Option Compare Database
' Constant for maximum number of columns EmployeeSales query would
' create plus 1 for a Totals column. Here, you have 9 employees.
Const conTotalColumns = 11
[Code] .....
If I say OK (rather than debug) after the error message, I can then click the button for the report again and it runs without complaint. And, as I said, the report with the added code never errors.
I have a networked database. It is accessed with computers that have both Office 2002, and office 2003. I get the following error message on computers with Office 2003:
"Object variable or With block variable not set"
It happens occasionally and the problem generally fixes itself so I dont think there is anything wrong with my coding.
And when it happens on the computers with office 2003 the ones with office 2002 can open the database fine.
What can be causing this?
Thank you in advance
Good afternoon all,
The following block of code was working great until I came into work this morning. This routine is called from the OnClick event of several combo boxes. When it does, I recieve the error listed above (Object variable or With block variable not set) on line:
If Me.cbxAss_Filter <> "All" Then
Any ideas? I'm running on no sleep in the last 24 hours so I'm sure I'm missing something simple
The code itself checks the contents of a form and builds a string of conditions which I apply to the Form's filter property to filter records.
Code:Public Sub CreateFilter() '************************************************* **** 'Name: CreateFilter 'Purpose: Generate a string to filter the form 'Inputs: None 'Outputs: None 'Instigates: Me.Filter ' 'Updated: 11/30/06 'By: Chris Lounsbury '************************************************* **** 'Vars Dim strFilter Dim lngLength As Long strFilter = Null strFilter = "" 'Each filter box has its own check for contents If Me.cbxAss_Filter <> "All" Then strFilter = "assigned = '" & Me.cbxAss_Filter & "' AND " End If If Me.cbxAction_filter <> "All" Then strFilter = strFilter & "action = '" & Me.cbxAction_filter & "' AND " End If If Me.cbxStatus_filter <> "All" Then strFilter = strFilter & "status_rsrch = '" & Me.cbxStatus_filter & "' AND " End If If Me.Combo34 <> "All" Then strFilter = strFilter & "rims_flags = '" & Me.Combo34 & "' AND " End If If Me.cbxAAMB_filter <> "All" Then strFilter = strFilter & "aamb = '" & Me.cbxAAMB_filter & "' AND " End If 'Check if filter string was built If strFilter = "" Then Me.FilterOn = False Else 'Determine length of Filter String 'minus the trailing ' AND' lngLength = Len(strFilter) - 5 'Chop off ending ' AND' and set the form filter If lngLength <= 0 Then Else Me.Filter = Left(strFilter, lngLength) Me.FilterOn = True End If End If 'debug 'MsgBox (strFilter) End Sub
I get this error message, " object variable or with block variable not set", when I'm in the design view of a form wanting to use the command button wizard. Using a brand new database/blank I imported a table from my database in question. I then tried to make a new form and put a button on it. It worked for awhile but then when I close the database and reopen it to add another button to the same form, I get the error message again. It isn't a problem that is specific to my homeschool database. It affects all the database I use/maintain as well as any new "test" databases I've made trying to figure out this problem. I looked at references before and after the error occurs and there is not difference. I've also looked at code but don't see anything code that requires variables to be defined. In the case of the test database with one table and one form there is no code to look at until I make a button before exiting and even then, I don't see anything weird.
I would appreciate any help you can offer.
I've attached the original database I was working on when the problems began.
Is it possible for a database program to adjust security levels on certain reference libraries when installed on ones computer, thus making all other database act up that are using those libraries?
Thanks,
Kris
I have a feild that requires firstname and lasatname for input.
How do I set it up so that is will takie the first name and Capitialize the first letter and do the same for the last name.
Ex: enter: john smith
needs to be: John Smith
Same thing for Company name
ex: the best company
need: The Best Company
I need it this way for a mail merge document I need to use
I am trying to do the following
If any one of you have experienced Abacus ticketing system, each reservation is comprised of a group of names as follows
1.1 Gul/Mohammad Atif Mr 2.1 Gul/Mohammad Adil Mr
3.1 Bond/James Mr
I have to copy the above data from the abacus tcketing system into a text box.
My question is how can i devide the above three names into three seperate text boxes.
If 2 tables have a field named 'EmployeeID' (for example), are you screwed when it comes to queries and vba, as far as selecting fields / specifying data goes?
Thank you!