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 RepliesBasically it's trying to take all the files in a given directory and add them into a existing database.
I have a database called Permit. the database has a field called "SEAL" and they will be creating an additional field "SCART" to hold the contents of the file that exist in the directory. Each image file, in directory, is named the same as a unique record in Permit.
example:
Permit-SC might have unique record 532442
An image file would be name 532442.
If the image file matches a record in Permit the image should be added to permit in a field called "SCART". At this point I'm not certain if I want a link or the actual image included in the record. since there's a possibility of including the permit file on the website it might be cleaner to have it included.
I have created a link to a CRM system that we have, and am using access as an interface for a specific task with the data contained within this CRM.
I have written a query that pulls to fields of data together but cannot get the two to calculate.
Within SQL server management studio I use the following query to give me a numerical response.
SELECTPRCPRICE,SUM(CAST(VL2BENEFICIALASNUMERIC (20,6))*CAST(PRCPRICEAS MONEY)))ASHOLDINGVALUE
Access doesn't appear to accept the cast functionality / how I can perform a similar functionality with access?
I'm trying to find all the table names inside an external access file from a path.
I have a code for the user to select a file which return a patch to the file.
Then I need to find all the tables names from that file and append them to a list.
so I them can input them into a code and link the tables.
here is the code to get the file patch:
Code:
Function getFileName(path) As String
Dim f As Object
Dim varFile As Variant
Dim path
Set f = Application.FileDialog(3)
[Code] ......
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 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 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?
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 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] ....
This is probably very simple and I feel I should know how, but I'm drawing a blank. How can I launch an external database when I click on a button?
Thanks
I am trying to move data in one table to a table in an external database and Access isn't playing ball. Supposedly you can use the 'IN' clause to link to an external database but if I put it in then I get 'Syntax error in INSERT INTO statement' and if I take it out it works fine not that, that is what I want. It's syntax seems to be defined like below at the MS website - jet reference???
I've also got a parameter that I'm trying to feed it, that is the path to the database, which is correct! c:helpcontent.mdb
Here's the query
INSERT INTO CONTENT_CLASSES IN '"+ @DBDir +"' ( PARENT_ID, CLASS_NAME, STATUS_ID, KEYWORDS, PRIORITY, LAST_CHANGED_BY, LAST_CHANGED_DATE, MULTIPLE_CONTENT )
SELECT A.PARENT_ID, A.CLASS_NAME, A.STATUS_ID, A.KEYWORDS, A.PRIORITY, A.LAST_CHANGED_BY, A.LAST_CHANGED_DATE, A.MULTIPLE_CONTENT
FROM CONTENT_CLASSES AS A;
Hi there I'm attempting to import records from an external database without losing the records that I currently have.
Eg if I have 3 records in my main database and 5 in the external one, I only want to append the 2 missing records and leave the other 3 alone.
Ideally what I want to do is the below
SELECT * INTO table
FROM [MS Acess;DATABASE=\pathexternal_data.mdb].[table]
WHERE id NOT IN (SELECT id FROM table)
Any ideas where I'm going wrong with the above?
Thanks for your help!!!
Tony
I was wondering is there a way to open up another database from within a database? Not to combine but just to open another one up instead of looking for it on a drive....
View 2 Replies View RelatedUsing access 2010. Trying to set up an import table from an external database.
Code:
DoCmd.TransferDatabase acImport, "Microsoft Access", "Y:DevTestFolder2013 01 Some Data Name", acTable, "tblName", "tblName"
I get an error 3024 could not find the file Y"DevTestFolder2013....
The database I am importing from is a 2003 .mdb
I tried the brackets because the file has spaces in it but still get the same error.
I've made an access project (ade) and stored the admin with password in the ade file.
When I open a new database I can use the import feature to import all tables with definitions and data from the ade file. All other objects are being locked.
How can i disallow tables from my mde to be imported into another access application?
I am getting this error message:
"Unexpected Error from External Database Driver" when I try to import some .dbf files into Access. I just did some quick research, and it was suggested that I remove or rename the borland driver. Not sure how I feel about attempting that, plus, where do I get a new borland driver once the old is removed/renamed.
I've got an Access 2010 application that currently links to a SQL Server 2000 database. I need to change the link to a SQL Server 2008 database on a different server. Where do I go to change the link? When I click on External Data, ODBC Database, I only get an option to import source data. I don't want to import any data. I just want the application to link to a different external database. If I go ahead and click OK, I choose my ODBC connection and log in to the 2008 database. Now I've got a list of "import objects" to choose from. I choose one table (just for testing; I actually want to link with all tables), it says it imported successfully, but when I view the data in that table from Access, it's the same old 2000 database data.
View 5 Replies View RelatedUsing access 2010. Trying to set up an import table from an external database.
Code:
DoCmd.TransferDatabase acImport, "Microsoft Access", "Y:DevTestFolder2013 01 Some Data Name", acTable, "tblName", "tblName"
I get an error 3024 could not find the file Y"DevTestFolder2013....
The database I am importing from is a 2003 .mdb. I tried the brackets because the file has spaces in it but still get the same error.
code that will allow me to take data [All Fields] from a Table in my Database and Insert them into another identically structured Table in another MS Access accdb Database.I would also like to filter for records older than a month [MyDateField]
I have tried the following code I found on line. It doesn't throw an error . . . but it doesn't perform the Insert either.
Code:
Dim ws As DAO.Workspace 'Current workspace (for transaction).
Dim db As DAO.Database 'Inside the transaction.
Dim bInTrans As Boolean 'Flag that transaction is active.
Dim strSql As String 'Action query statements.
Dim strMsg As String 'MsgBox message.
[code]....
We have an older system that generates txt reports. I take these txt reports, then using Excel, I append the report headers to each record... creating a datatable. I then import this table to Access. I have not been able to figure out how to have Access perform this. In Excel, I use an "if" statement.
View 9 Replies View Related