i have below text file.. i want to load this on a access table
'EQD+CN+BFSU4533400+4500:102:5++2+4
'RFF+BN:244390
'DTM+7:200708201032:203
'LOC+165+AEJEA:139:6+11:TER:ZZZ
'MEA+WT+G+KGM:4200
'EQD+CN+BAXU2625601+2300:102:5++2+4
'RFF+BN:244388'DTM+7:200708201039:203
'LOC+165+AEJEA:139:6+11:TER:ZZZ
'MEA+WT+G+KGM:2300
'SEL+N/A+CA
can someone tell me what is the best way!!
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.
When I try to transfer (ctrl c + v) data from a table in Excel to a table in Access it loses format.
Exemple: $ 1.000,00 (Excel) become 1000 (Access).
I need to keep the format to make sure that the code works properly.
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?
Thanks for your support
GinnyP
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.
PS: I am using Access 2010
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
[code]....
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.
View 2 Replies View RelatedDear All
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
Any idea how I can solve this problem?
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.
Thanks
Sam
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!
Arpeggione
I have a table and a form. I need to input data in the form and click on a button to add this data into the existing table.
This is part of a bigger application which will be compiled and given away for use.
tblProduct
id = autonumber
name = text
InHand = integer
Price = float
frmNew
txtname = textbox
txtInhand = textbox
txtPrice = textbox.
I need to coy frmNew.txtname into tblProduct.name and so forth.
Hi There
I Have Created An MDE File But Still If Someone Wants To Import my Datatable Can!!! How Can I Prevent This From Happening?.......
Found this site on google, looks pretty good, hope you can help me.
I need to update some data but am at a loss on how to do it.
Here is my data...
Column1......... Colum2..... Colum3
Name 1 .......... 1:30 ........ 2:30
...................... 2:30 ........ 3:30
...................... 3:30 ........ 4:30
Name 2 .......... 1:30 ........ 2:30
...................... 2:30 ........ 3:30
...................... 3:30 ........ 4:30
Name 3 .......... 1:30 ........ 2:30
...................... 2:30 ........ 3:30
...................... 3:30 ........ 4:30
basically I need to copy the Name 1 to each of the rows under it so the new data should look like this it originates from an Excel spreadsheet and I need to bring it into an Access Database.
Column1......... Colum2..... Colum3
Name 1 .......... 1:30 ........ 2:30
Name 1........... 2:30 ........ 3:30
Name 1........... 3:30 ........ 4:30
Name 2 .......... 1:30 ........ 2:30
Name 2........... 2:30 ........ 3:30
Name 2........... 3:30 ........ 4:30
Name 3 .......... 1:30 ........ 2:30
Name 3........... 2:30 ........ 3:30
Name 3........... 3:30 ........ 4:30
any help or ides would be appreceated.
Thanks!
Found this site on google, looks pretty good, hope you can help me.
I need to update some data but am at a loss on how to do it.
Here is my data...
Column1......... Colum2..... Colum3
Name 1 .......... 1:30 ........ 2:30
...................... 2:30 ........ 3:30
...................... 3:30 ........ 4:30
Name 2 .......... 1:30 ........ 2:30
...................... 2:30 ........ 3:30
...................... 3:30 ........ 4:30
Name 3 .......... 1:30 ........ 2:30
...................... 2:30 ........ 3:30
...................... 3:30 ........ 4:30
basically I need to copy the Name 1 to each of the rows under it so the new data should look like this it originates from an Excel spreadsheet and I need to bring it into an Access Database.
Column1......... Colum2..... Colum3
Name 1 .......... 1:30 ........ 2:30
Name 1........... 2:30 ........ 3:30
Name 1........... 3:30 ........ 4:30
Name 2 .......... 1:30 ........ 2:30
Name 2........... 2:30 ........ 3:30
Name 2........... 3:30 ........ 4:30
Name 3 .......... 1:30 ........ 2:30
Name 3........... 2:30 ........ 3:30
Name 3........... 3:30 ........ 4:30
any help or ides would be appreceated.
Thanks!
I need to number contacts by group example
pri key account number and contact number
123456 #1 john doe
123456 #2 jane doe
123456 #3 robert doe
123456 #4 mary doe
123457 #1 fred smith
123457 #2 mary smith
123458 #1 tom jones
123458 #2 nancy jones
123458 #3 batty Jones
Greetings,
I just took ownership of an older Access 97 DB and I'm having issues with one of my tables.
For example, in my inventory table, I listed a 15" F/S monitor and a 17" F/S monitor. When I use the drop down arrow to assign the correct monitor to the user, either the 15 or 17 the whole "monitor" table goes to whichever one I choose. :confused:
Any insite would be helpful. Thanks for your time. :)
I am storing addresses form a large address book
Can access handle 60 to 70 thousand address?
Thanks
Ron
Hi I am new to this site and with some knowledge with MS Access but not enough to fix this issue.
This is the scenario. User is attempting to load the ODBC driver to connect to a MS Access table located on her network drive within our internal LAN. So she loads the MS Access ODBC and it gets a error message 01000 about TCP/IP something if I remember right. And she cannot connect to the table on the network drive.
This table we are trying to connect to in our network drive then is linked to a MySQL server with PHP hosted by a 3rd party outside our network.
Question: how can I load the ODBC driver to connect to the table on the network drive within our LAN? What are the steps that I need to take...
thank you,
Carboncopy