I have linked ms access to orcale to run a query, the date in orcale is in this format 20140101, i have tried to convert the date in the query like that but it's not working. format(mydate,"yyyy/mm/dd".
I am an Oracle DBA with little to no Access experience so please be kind. :D
As of yesterday a frequent user of Access 2003 (11.8166.8172) SP3 reported that all columns in all rows have the value #Deleted in a number of tables linked back to an Oracle database.
I am not experiencing this problem but I have Access 2007. It is not an option for this user to upgrade.
I've discovered several issues over the web with data type issues/ characterset issues but nothing seems to fix the problem. I've tried the Oracle supplied 11g and 10g ODBC drivers. I've tried the Microsoft supplied ODBC for Oracle drivers.
None of it seems to work and I am out of ideas. Can anyone suggest anything?
The problem wasn't noticed until yesterday so it can't have been happening for long. The problem is occuring in Oracle 10.2.0.3 and 10.2.0.1 databases. There has been no change in the Oracle environment.
There was a windows update recently but we tried uninstalling that and it didn't help.
I have linked tables from an Oracle database. I want to run a query to find records that have dates in a defined range. The date field in my linked Oracle table is in the date/time format. When I run my Access query, I only get those records that have a date (and no time) in the field. How can I get all records, even those with a date/time entry?
I have developed a Microsoft Access 2010 database for my client and the database is split with Front-end/Back-end, the Back-end and the database is shared on Network, The client operating system and applications for all users are hosted and consistent and the service is delivered over Citrix.
The database some times corrupt the tables record and give a permanent #Delete Error, I have attached one of the database table and the screenshot of the error,
Not sure if this is possible but I have in my db a linked ODBC connection to a table. I use a Oracle 8 login to access the linked table. My question is can I capture the user login in ID somewhere in my table to show who is logged in? Thanks...
I'm making a MS Access frontend for some tables on the Oracle 8 database at work. The tables are linked ofcourse.
One table has an AUTONUMBER field on the Oracle and it seems to give me trouble to insert new records. When I try to insert a new record (leave the autonumber field blank) I get the following error: "ODBC — insert on a linked table <table> failed. (Error 3155)" followed by the error "[Microsoft][ODBC Driver for Oracle][Oracle]ORA-01722: invalid number (#1722)".
When I look at the Oracle documentation I got this: ORA-01722 invalid number Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates. Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation. I checked the INSERT statement: "INSERT INTO AFM_HV_PROP_VALUE (HV_INST_ID, HV_PROPERTY_ID, TABLE_NAME, HV_PROPERTY_VALUE) VALUES (4, 'V_TESTJE', 'hv_inst', '123465')" and everything seems to be allright. The value that's causing the error is the "4" that gets in the "HV_INST_ID"-field. Using TOAD to execute the SQL Statement, there is no problem at all.
When I look at the table design in MS Access I see that the Autonumber field is of the type "Double". That doesn't seem right to me...
Anyone some suggestions? I'm running out of courage :s
1. I have linked a table which does not allow me to update any entries. 2. In this linked table I have to update one column that contains all null values 3. I have another table that contains the values required for that one column in the linked table
When I try running an update query after doing an Inner Join, i get the Operation must use an updateable query error...is there ANY work around at all ? Please let me know . any help would be greatly appreciated.
I dont use linked tables very often, and when I always seem to run into the same problem.
I have two tables. The pri table has an index key defined as an autoindex, with no duplicates. The sub-table is linked to the pri table key in a many-to-one relationship. The linked field in the subtable is defined as a long integer non-keyed field.
All is okay, until I attemp to add a SECOND record. At that point I get a 3022 error, stating I cant do something because I am attempting to create a duplicate key.
I would love to fix it, nut I am not sure exactly what the problem is that needs to be fixed. The pri key would not SEEM to be getting duplicated, and the subtable linked field is not keyed. Hmmm, I guess I need a steer here please.
My tables are sybase linked tables to my Access front end. I get this error whenever i click away from my mainform while my not-null fields (textboxes) have not been entered. So, i would like to put an error message "somewhere" that tells the user to fill in the necessary fields and highlight the necessary textboxes in red, instead of this scary ODBC message http://img82.imageshack.us/img82/6829/error0el.png :eek: :eek:
When i put my error handling in the afterUpdate or afterInsert Event. I still get the above error msg instead of my msgbox. Where do i put this message? or should be question be, "what the proper way to handle this intimidating error? :)
Here's my error handling:
On Error GoTo Err_Form_AfterInsert
'do something
Exit_Form_AfterInsert: Exit Sub
Err_Form_AfterInsert: MsgBox Err.Description, vbExclamation, "Please enter the Project Name, Project Leader Initials, Main User and Status Code" & Err.Number Me!Combo67.BackColor = "red" Me!Combo65.BackColor = "red" Me![Project Name].BackColor = "red" Me![Project Leader Initials Combo].BackColor = "red" Resume Exit_Form_AfterInsert
I have ODBC linked tables to a DB2 database.When I try to edit the values in some of the tables (open the table in dataview and edit one record) I get a 3021 Error -No current record.On other tables (linked the same way) I don't get this error
If I try the update by running a query on the same table, I get no errors.
The system I have is a german Access 2010 on german WinXP and a connection to DB2 LUW v9.7
Is there any set of settings necessary for ODBC to work with access and vice versa?
I'm new to Access2007. I created 5 tables all related to each other in series with one-to-many joins (type 2 and all enforcements on).So an Id in table 1 can show up several times in table 2, and the same is true from tables 2 to 3, 3 to 4 and 4 to 5.I want to enter new records in such way that I can see all tables at once. I tried to do that with a query, but I can only modify records, not add new ones.I have no problem adding records to individual tables but have not figured out how to do it to this All-tables query.
Due to software licensing restrictions, my (Access 2007) development pc is NOT (may not be) connected to the network.The user pc's are all connected to the network.The network pc's only gets Access 2007 Runtime.The Front-end accde will be located at C:mydbFE heFE.accde on each user's pc.The Back-end will be located at X:mydbBE heBE.accdb (network share).
Currently (during development) the Front-end and Back-end is at C:mydbDEVFE and C:mydbDEVBE respectively - on my development pc, and the linked tables are pointing to C:mydebDEVBE.Now that I'm ready to distribute the database to the user pc's, I obviously need the Front-end's Linked Tables to point to X:mydbBE heBE.accdb
Where does Access2007 store the path to the Linked-Tables ? Is there a way I can change this path manually before making the accde ?I noticed that the path to the linked tables appear in the (hidden) MSysObjects table, but I do not want to mess with it until I know what the solution is.
i am using access against an Oracle DB through an ODBC driver, and when appending records to a local table, if access gets an Oracle record error "#deleted", the append query aborts. . . that's fine, but I can't figure out how to trap the error to get the rest of the records. . .
Our master database has linked tables with ODBC to our SQL database and linked tables to another access database. On the computer i'm running this from a System DSN data source has been created to connect to our SQL database.
The problem I'm having is when I run Macros to kick off Queries, there seems to be some kind of timeout issue and its not consistant. Sometimes in Access after a hour or two, the ODBC links somehow just fails. I have to go up to the menu and go to 'Get External Data' and then 'Link Tables' and relink the ODBC DSN connection. I make sure to hit save password and the end but that doesn't seem to do much. This also happens sometimes when I close and reopen the database.
But it seems erratic, sometimes it can stay up for hours but sometimes just a few mins. Sometimes I can close the Access database and reopen without losing connection. Being so erratic, this makes it very hard for me to run batch files nightly to kick off Macros. Is there some kind of inactivty timer for DSNs? Or maybe with the SQL database itself? Or is the way i'm restablishing the ODBC connection wrong ?
I have built a database and within it my queries I use for reporting make tables in a separate database. This allows me to work in my data base and update info as needed. My problem is that my make table queries wont run if anyone has one of the report pivot tables open. I have added macros to all the Excel files that pull from the reporting DB but I still have several times when I cannot update because someone is using the pivot table, Is there a way to allow me to update the report DB's while the pivot tables are open? I have tried using manual refresh methods and disabling auto update on open but the result is the same.
I'd like to secure my frontend by disabling access to the linked tables, queries etc. I thought converting to .accde would do this but apparently not. Is there a way that this can be achieved?
I am aware that I can disbale the ribbons etc but they can all be turned back on using the options menu.
I have made a link through ODBC to a Oracle table but when I view the table through Access 2003 I don't get the same data, some postings are dubble and others are missing. The number of rows are correct and it is also possible to create reports with a correct result.
I have also created a Form to be able to add and amend posting in the Oracle table, but the result is the same as when I just view the table, missing and dubble postings.
I'm trying to link to Oracle through ODBC...everyhting is set up, and I can connect and I can also see the tables..The problem is...there are just toooo many tables in the link window!!!....
Does anybody know how to find the right table you want faster??? Or how to limit the tables that are retrieved from Oracle to just the ones I desire?
I even set up the correct Schemas to just view the tables I want...but It won't work when linking it through ODBC!..All the tables appear over and over again... And the list is just tooo long.
I have written a few solutions in Access (using access 2007). Now I going to create a few new ones for my company. We use Oracle for our main database and was wondering what would be the advantages and disadvantages of using Oracle as my backend while Access is my front end?
Also, should I create the queries in Access or in Oracle?
finally, is there anything I need to do differently?
Ok. I have tables vinculate to ORACLE, the primary key of Oracle is double, from Access of query return one number rounding 1,00000902026541907589E+20 this is the problem, in query i have this funtion: IIF(isnull([CAMPO1]);[CAMPO2];[CAMPO1]) this return one number ok field(CAMPO1) and other bad(CAMPO2 it's rounding). I need that return this query two doubles. example:
bad - 1,00000902026542E+20 = 100000902026542000000 ok - 1,00000902026541907589E+20 = 100000902026541907589
We have an MS Access 2010 Database that uses Local Tables, External MS Access Tables, and ODBC Linked Oracle Tables (Accessed for Read Only). The unsual issue occurs with the ODBC Linked Oracle Tables.
One of the more important aspects of the project is to modify the ODBC Links to point to upgraded Oracle Database Tables. Up until today, all of the Links had been able to be remediated with a simple refresh and test.
This morning, however, it was determined that one of the Database Tables did not exist in the Schema. Instead, it was an Oracle Synonym for a Table that existed in a different Schema. We believe that we have the proper authorization for access to all of the Schemas involved, and despite this fact, MS Access was unable to link to the Table properly.
I have to use MS Access as front end with ODBC connection to Oracle 9i
DB.
The application(forms) should be able to update, delete ,insert records into oracle tables(backend).
i have a main form,which has some unique id's and other info about the ids and the subform shows several matching id's for that unique id in main form. the user who uses this application should be able to 1) search for the unique id in the main form such that the subform displays all its matches 2) they should be able to select anyone match and say approve(there can only be one match), then that particular record should be updated in the table.IF I USE A CHECK BOX AND IF THEY CLICK ON ONE RECORD AS MATCH,HOW DO I TAKE THAT RECORD SAY THE ID , NAME ADDRESS AND ALL DETAILS AND UPDATE THE TABLE??? similarly when they select some other record i should give option of deleting other irrelevant matches in the backend table.
the main form and the subform uses the same table as source.updates are to another table, i should also have to put entry into audit table about which record was deleted and which one inserted..
How should i do this?? i am new to MS access .VBA, any help would be highly appreciated!
I am attempting to set up a saved import procedure in an Access database that will be run programmatically using docmd.RunSavedImportExport. The source document is an Excel file.My goal is to trap any import errors caused by inconsistencies in the source data. I'd also like to provide the user with some small amount of feedback on what is causing these errors.
My initial thought was to make use of the "File_ImportErrors" table that is generated following an unsuccessful import. *Not once* has this import procedure consistently generated an error table - sometimes the table is created, sometimes it isn't.
I have tried changing the file format of the import file to comma- and tab-delimited files, respectively. Both of these formats do consistently generate an error table, however none of the error values in the table are accurate - it reports a slew of type conversion errors for fields that are completely consistent and unproblematic, whilst completely overlooking conspicuous errors from text strings in date fields.
I have tried the same import, both procedurally and manually, using all available Excel and text-delimited formats, with the same respective results.I notice that nearly every thread on AWF pertaining to error tables discusses how to delete them, rather than make effective use of them. I suspect this is perhaps why?
