I have a database that's almost finished. I've built a few forms for the major parts of the database, but some other data is only changable through the tables directly. The remaining tables (without corresponding forms) are pretty small and shouldn't require changing that often.
Assuming I put user-level restrictions on the database, would it be okay to allow certain users to directly add/edit/remove entries to these tables?
My instincts tell me I should make a simple form for each table, but I'm looking for the path of least effort.
Is it possible to direct the placement/order new of fields when modifying an Ms Access database in code?
I need to modify the schema of an MS Access database via code - but I want to be able to direct the order or placement of the fields within the tables.
For example - if TableOne has 3 fields - Field10, Field20 and Field30.
I would like to be able to add say Field15 between Field10 and Field20 - not just append it to the end of existing fields.
I believe it is possible as you can do it within Ms Access itself. I can use ADO, DAO, ADOX or SQL for that matter - but it seems all of these offer no placement of the field within the table.
I want to create a combo box that links direct to a table, rather than going through a query. Then when you select the record from the list it updates a text field to show the memo field of which the selected reccord is related
Can any one help
I tried using a query to do it, however queries only ever allow a maximum of 255 charcacters in the fields basically the code looked like this
cboAfterUpdate()
me.txtfield = me.cboselection.column(1) ' because the data was in the seccond column end sub
but as you can guess this only allows 255 characters to come accross from the memo field
How much formatting can be done to a report from Access into Excel? I am trying to (or will be in the next day or so) to create a report to export data and I would like it presented in a specific format. This is hopefully to replace a spreadsheet where someone currently has to collate and re-type a load of info that has already been typed into various other spreadsheets.
Access is going to happily cope with all of those users entering their data to a table, and I would like to be able to output that data to something similar to the end result now? Is there a way to have an Excel template file and simply direct Access what to put in which cell?
I'v looking for since a couple months a go to make a report direct from access form using crystal report but i havent found it yet. I'v tried this code and its giving me errors. " run time error 1004 method range of object _global failed "
how to make a report using crystal report direct from ms access as front end application ? is it possible to use crystal report ?btw i use database sql server 2008 and MS Access 2007 as my frontend application.here's the code that i'v found and gives me an error
Dim CR As New CRAXDRT.Application Dim rep As CRAXDRT.Report Set rep = CR.OpenReport(Range(" ??? ")) * i getting error in this line, what should i do to fill it ?? rep.ParameterFields(1).AddCurrentValue "Boston" rep.ParameterFields(2).AddCurrentValue "Cars" rep.Database.Tables(1).SetLogOnInfo "tool", "db_tsel" rep.ReadRecords rep.PrintOut promptUser:=False, numberOfCopy:=1 ' promptUser:=True doesn't work
I currently have a form in Access 2003 which is filled with quite a lot of data. Each new form need to be approved by some people, and I send them an email through Access to ask it. Is it possible to have a direct link to the form filled with the particular data they need to approve without creating a website?
I got a field which is defnined based on a query result to ease the user input. However, some input are not in the query list, if I input data directly to the field, ACCESS complained I must choose item from the query result. Is there any way that the user can either select from query result or direct input to that single field?
i have a database in access 2003 when i open it with access 2003 it shows data in table but when i open same table in access 2007 it shows only header rows , no data
how can i see this data into access 2007 or excel 2007.i want to link these table data with excel 2007 or access 2007 but with above problem i can't do it
I've imported an Excel file into Access as a textfile. All of the information on the Excel spreadsheet was successfully imported in Access. The only problem that I have is that some of the fields on the table have quotation marks (e.g. "Stults, David, "KWK, Inc.", "7,860.27", etc.) My first question is whether these quotation marks can be removed from the table.
My second question - There is information on this table that needs to be manually entered on a data entry screen in Access. Is there anyway to automate this process?
Attached is a screenshot of the relationships in a database I built a couple of years ago. It's worked absolutely fine in Access 2003 and currently has over 18,000 customers with associated information in it.
However, when I open the database in Access 2007 the performance is awful. All the forms are very slow to respond when tabbing between form elements. I've experimented by reducing the number of form elements calling on related data on a given page and whilst this improves performance it reduces usability - something I don't want to compromise on especially since Access 2007 should be able to cope with this.
My next question is therefore whether I've got the most efficient underlying table design and I can't see any other way of doing it than my current method so I'd be grateful for any feedback or advice anyone has.
I have an MS Access accdb with linked SQL Server 2012 ODBC tables. I am working on a procedure to copy data from local tables to these linked tables (identical schema). I did a simple
Code:
DoCmd.RunSQL "INSERT INTO linkedTable SELECT * FROM localTable"
This works, but is very slow. Way too slow. (INSERT copies the data one record at a time).
I would like to copy the data in a bulk operation, or operations that I can execute programmatically.
Hi, I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?
If you want to use a "DoCmd.RunSQL "INSERT INTO" command to insert data in a table and the data to insert comes from a table and a form, could this be done in one pass?
So...writing a record wit 4 values from table1 together with a additional value from a textbox in table2 as 5 values.
Is there a way to append a pivot table to a table or possibly make a query based on a pivot table? I need to get a count of Part Numbers and I need the average price for all these parts. Additionally I want to ignore a count of less than 3.
Also I am having trouble filtering on the count in the pivot table... haha, so I was gonna Query on it later on.
How to update data from one table to another table using form.
I have data coming from design team in Database 1 and using form i want search data and assign the job to a person and store it in the database with his name. I have to do this because database from design team is read only.
I have two tables (Access 2010). One with a list of names (List1) and another with a very similar list of names (List2), but they differ in very small ways. For example, List1 might have John Smith, and List2 would have Smith, John L.; and Smith, John. List2 also has a unique ID associated with these names that I need to append to List1.
I need to design a form that will allow me to look up names in List1, and have it return all names that are similar in List2. I then need to be able to choose with record in List2 matches with the List1 entry (based on a few other columns in List2, such as birth date) and have the form add that unique ID to the List1 record.
I am rebuilding an application for a client and I have an Access table that I am using as a temporary table. Once the user is done entering information into the temporary table through a form, the user presses an update button that appends the records using an Append Query in Access to an SQL Server Table.
The following error message occurs:
"ODBC -- insert on a linked table 'linked tblname' failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Explicit value must be specified for identity column in table 'linked tblname' when IDENTITY INSERT is set to ON. (#545)
I am using a form, subform combination to record a bill with many details. The bill summary is posted into a tbl_TransactionsMain table in SQL Server using the ADO AddNew method. The PK for the tbl_TransactionsMain is then entered into the temporary table in Access. When the temporary table records are appended into tbl_TransactionDetail the error message occurs.
What is also interesting is while typing out this post I thought to test the error by manually trying to run the query. The query worked like a charm! :confused: When the orginal error occurred off of the form I tried to run the query manually and it failed. I am guessing that this might have something to do with the ODBC timeout.
I think SQL Server/ODBC connection is not liking how I have a set of records in an Access table with foreign key numbers assigned when I am attempting to append the records. I am new to SQL Server and any ideas are most appreciated! :)
I have created a code below to test whether I can run a query and retrieve a data from an SQL server table. And so far I can return the result using a messagebox. but somehow I just don't know how to use this connection to update the table inside this access file. Basically I want to use this as a front end file. then when the form is open it will automatically update the table inside this access file and load the data to the combo box as a list.
Code:
Option Compare Database Sub LocalServerConn_Test() Set conn = New adodb.Connection Set rst = New adodb.Recordset
I have two tables, submit and imgdest. Submit is edited by front-end users to load pictures for back-end users who then delete the images when they're done with them. Submit is edited by a form, in the form I've placed a button (Command37) that has code:
Code: Private Sub Command37_Click() Call InsertData MsgBox "Completed", "0", "Completed Backup" Exit Sub End Sub Private Sub InsertData()
[code]....
This code was working for a short while, now anytime it's run I get error 3027 - Cannot update. Database or object is read-only. However, I can open the linked table and manually change information in it with no problems.
I have a database with existing tables.I now want to add a whole bunch of records into my access tables.A lot of the data is repeated, and lends itself to my doing it in excel with it's better copy and paste abilities, and it's ability to easily increment alphanumeric fields.I can't seem to succeed in append importing to the bottom of my access table from my excel spreadsheet. All columns are the same.Can this be done, and if so how please?I'm on XP Pro with Office 2003 Pro.I'm also very much a newbie in Access.
I want to connect an Excel table with an Access table so when I make changes to excel table these changes to appear in the access table and use the access table for my web site. The excel table looks like:
Room Type01/05/06-06/06/06 07/06/06-15/07/06 21/09/06-30/09/0601/10/06-31/10/06 * Standard Single 26 34 * Standard Double 44 59 * Standard Triple 60 80
When I try to connect this table with access I am getting an error reffering to the second line of the excel table where there are not numbers but dates. The access connected table looks like:
Room Type01/05/06-06/06/06 07/06/06-15/07/06 #Number! #Number! * Standard Single 26 34 * Standard Double 44 59 * Standard Triple 60 80
One of my user gets the following error from MS Access running from his local machine and linking to the database & appropriate tables. The front end MS Access application uses back end SQL server to get info from. ODBC connections are in place, folder/file NTFS security for this user is also in place. he is admin on his PC. His machine uses DHCP to connect to the network. One thing I'd like to mention is: We have Citrix server on which this application is published and this user can connect and perform the same tasks using the Citrix client. He is having errors in connecting to the tables Only when he launches locally.
Recently he got new upgraded PC and since than this problem arose. He was OK on his older PC. Any ideas what & where else to look?
In my opinion, it must be some tweak that needs to be done on user's machine.
PS: The problem appeared only on this user's new PC, from his old PC he was able to get to the tables and all other good stuff. All other users in the same group do not have any problems at all.
I have most of my data in DB2 tables which I am linking to. However, there are a few pieces of data I am gathering from text files. I put them into a little Access table and tried to JOIN with a DB2 table field. My query died....
Is Access amenable to joining different types of tables? If anyone can advise I would be most grateful!
