Hello to all,
What is the simplest way to create an empty table with the same characteristics (same fields number/names/properties) as an existing table and how to rename / delete a table ?
I have a table (A) with autonumber primary key, with vba code i want to copy it in an other table (B) then i want to delete (A) and rename (B) to (A) all this to renumber autonumber primary key.
Each time i open the form and quit it without enter data i lost a number so sometimes i want to re-organize the table to renumber and compress autonumber.
Thanks in advance for help.
VINCENT
We use an Access 97 database over our network. We need to put it on additional PCs, but do not have current licenses for this.
Is there an Access 97 clone that we could use? It wuld simply need to serve as the Front-End of the database and be able to connect safely to the MS Access 97 Back-End.
I have some extensive experience with databases and even Access when used as a datastore but I have never written an app with Access as the front end so i'm very much a newbie. I am doing a friend a favor and fixing his Access app which someone created for him and he needed to add a field to it. I am hoping some of you Access Experts can help. We added the field and everything seemed to be working fine except know the main form for looking up and editing users seems to be broken. The main form is tied to a query which basically returns all the records in a table in last name order. The lookup is accomplished via an unbounded combo box. The AfterUpdate event for that combo box is below. I have added some troublshooting code to the sub below. The problem is that after the "Set rs = Me.Recordset.Clone" line, the recordset is empty. I checked by using a message box to return the recordcount. Once it gets to the "Me.Bookmark = rs.Bookmark" I get "Run Time Error '3021' No Current Record"
Any help anyone can give would be greatly appreciated.
--------BEGIN CODE SNIPPET------------- Private Sub Combo81_AfterUpdate() ' Find the record that matches the control. Dim rs As Object
Set rs = Me.Recordset.Clone MsgBox ("RecordCount: " & rs.RecordCount)
'testing debug MsgBox ("Member ID Searched: " & Me![Combo81])
rs.FindFirst "[tblMembers.MemberID] = " & Str(Me![Combo81]) Me.Bookmark = rs.Bookmark End Sub --------END CODE SNIPPET-------------
Hi Everyone, I was wondering if anybody would be kind enough to help me with a problem that I have with regards to a 'Contacts Database' that I found under the Sample Database link on this site - posted by MStCyr. When I try to navigate through A-Z buttons I am presented with a dialogue box which states " the object doesn't contain the Automation object 'RecordsetClone', whatever that means??
I would like to use this sample DB in my project however it is presently unusable unless I can figure out how to clear this problem. If this is not achievable could you please point me in the right direction where I could find a similar, downloadable file.
Hopefully a more experienced forum member than I could please help me out.........
Overview: Ok so i have document database, and i have a number of forms these forms have a tab control layout on them. There is a 'Search' tab, a 'Add' tab, and a 'Edit' tab. Now what i have is a edit log table linked with the documents table on two seperate subforms on the 'Search' page. They are linked by the 'Document Number' which is not the primary key. The 'Add' page is linked to the document subform and the 'Edit' page is linked to the Log subform.
Aim: My aim is to have a changes log for each document so that when i select a document in the document subform that the all the changes made to that document are shown on the log subform.
Problem: When i open the 'Search' tab there are somtimes more than one record with the same 'document number' in the log subform. This is normal as users may update the same document more than once causing there to be 2 or more of the same document number listed under the log subform; but... this also causes the document subform to display a duplicate of the document with that 'document number'.
Proposed Solution: I dont know if there is a way but i was wondering if there is a piece of code that will automaticly hide any records that have the same document number on the document subform. Or if there is a better way please tell me.
Thanks for taking the time to read this, i hope i havent wasted your time by asking a simple and stupid question.
I have a form with a continuous subform whose RecordSource is a predefined query. There are a number of controls on the main form which allow for various filtering on the subform / query and a command button to export the data to Excel.Because I want to export exactly whatever the user has filtered using the form, I take a clone of the subform recordset and pass that as a recordset object to my export function.
I've just noticed, however, that a couple of the fields in the Excel spreadsheet are always blank, even though the corresponding records on the subform show values? I can't figure out why; there is nothing unusual or distinctive about these fields (2 x Text and 1 x Date/Time) other than the fact that the contents don't seem to copy? I do get the field names - just no data for them?
The data is visible in the subform, and when I run the query on its own, the fields are populated in the returned dataset as well? So why aren't they included in the RecordsetClone?
I need to get x and y coordinates for each device, but the data has to get looked up from 2 other tables.
I have a table (called InstReclosers) that has device names. Each device is on a Section. I can go to another table (called InstSections) and look up what Node that particular section is tied to. Then I need to go to another table (called Nodes) to get the X and Y location for that particular node.
How I can go about getting this X and Y data into the InstReclosers table?
...in summary, InstReclosers has device name and section name. InstSections has section name and node name. Nodes has node name and XY coords. Need XY coords for each device in InstReclosers.
I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).
Currently, I setup the query to pull info from the form field like this:
DateField: [Forms]![frmmain]![DateField]
However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).
Here's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)
Code: SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next FROM calls WHERE (((calls.firm_id)=[firms].[id])) ORDER BY calls.called DESC , calls.next DESC;
When I run the thing...I get a dialog asking me for firm id.
I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.
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.
I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...
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?
When the value "X - DISCONNECTED" is selected in the form I want the record to be removed from its existing table and sent to a new table which keeps all the "X - DISCONNECTED" records together.
Any ideas would be greatly appreciated. How would this be coded?
I'm building a make-table query for which if the result is null (no record correspond to the set of criterias), a default message like "there was no activity during the period" would appear in the table (not a message box...I need the message in the output table). The best I could think of is an IIF function but it doesn't seem to work... Is there any way to do this without using VBA?
In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.
the data has a fixed part lets say
Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this
Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8 Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...
I have one DB that is used for creating/storing customer ID's, and another DB that is used for creating/storing job information for customers.I have linked the table from the customer DB to the job DB.
There is a table in the job database that holds customer name and ID, and some VBA that generates unique job codes.
Is it possible to have the data from the linked table automatically update into the existing table?
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've got a table tblPatienten, a table tblRechnung and a form frmRechnung.
The primary key in tblPatienten is KundenNummer, the primary key in tblRechnung is RechnungsNummer. The relationship between tblPatienten and tblRechnung is one to many.
Now, every patient (stored in tblPatienten) is allowed to make multiple orders (stored in tblRechnung).
How can I assign each new order entry in frmRechnung to an existing patient in tblPatienten?
I have a form with a sub form. when a record is choosen in a combo box the sub form is filled out with a record.
what I am trying to do is have a button that will copy that record to a history table then delete it off the the main table.
I cheated by using the wizard to get the code to delete the record but I am having troubles modifying the code to copy that record to the history table. Here is the code below. I have tried to insert code in several places but it just errors out.
'------------------------------------------------------------ ' Master_tbl_sub_fm ' '------------------------------------------------------------ Function Master_tbl_sub_fm() On Error GoTo Master_tbl_sub_fm_Err With CodeContextObject On Error Resume Next