Saving Manually To ODBC Linked Table?

Mar 17, 2005

Hi,

I have built a form without a record source as i need to mess around with the fields before i save.

On save, i would like the form to save to an ODBC linked table in my database.

I save the record through a save button which has the following code attached to its on_Click event.

' NOTE: DAO 3.6 code
Dim db As Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblODBCLinked", dbOpenTable)
With rs
.AddNew
.Fields("Country") = Me.txtCountry
.Fields("ID") = Me.ID
.Fields("Creation Date") = Now()
.Update
End With
rs.Close
Set rs = Nothing

I have no problem saving to a non-linked table with the above coding but always seems to fall over if as soon as i try to open the linked table. Ive debugged and the problem seems to be with opeing the table.

Set rs = db.OpenRecordset("tblODBCLinked", dbOpenTable)

Thanks in Advance for any suggestions

Access 97 / Windows NT / Full Read Write access. Not a permissions issue

View Replies


ADVERTISEMENT

Cannot Create New Table Linked To ODBC Database

Mar 7, 2006

I can edit/open existing linked tables (to Sage Line 50, v12) but not create new ones.

Following the procedure:
- New
- Link Table
- File Type: ODBC databases ()
Nothing now happens!

I can open the Link Table Manager to update previously created links.

Any ideas?

I'm using MSAccess 2003 on a brand new machine.

View 1 Replies View Related

Odbc Connection On Linked Table Failed

Feb 9, 2008

I Need help,

I have an access 2002 front end and I am linked to a sql server express 2005 backend and in one of my tables I am getting this error when I add a record to a work order table. The error is ODBC connection on INSERT failed on linked table.

A few strange rhings happen. It only involves customers with over (approx.) 140 work orders (there is a one to many with customer to work order). All other customers seem to be immune to this.

Also, if you go to the tables directly from a access database window, you can still add/edit or delete records, but in the form view, it gives you an error.

I have tried several things including changing code (the orgional code was set dbs= currentDB and dbs.execute("INSERT.......)

I have found similiar posting but no real direct causes. does anyone have any ideas on how to resolve this?

Thanks

Frank

View 1 Replies View Related

Local Table And ODBC Linked Table Don't Have Same Filter Options

Dec 31, 2014

In a local DB table or a data grid view, the columns have an arrow at the top next to the name. When the arrow is clicked, all the entries in that column are displayed with check boxes. They are listed underneath the "sort" and "text filter" options. I am working on a DB project with ODBC linked tables instead of a local access table.

My linked table does not have this same functionality. It is missing the names with check boxes where I can select individual entries. I don't know the correct terminology for this functionality I am describing. That makes searching tough. What this is called and why the tables would be different.

View 3 Replies View Related

Auto Fill With Data From ODBC Linked Table

Jun 8, 2007

Hi All,

Although familiar with CR, SQL and other such things. I have never had cause to use Access all that much.... Until Now!

My problem is easiest explained by my requirements....

I have a db table called Source. The data for this Table is populated by a form called Technical. Technical containes a series of fields (SO No, SO Line No, WO No etc etc). Some of these fields are lookup fields linked directly to the SO_Header Table in our main ERP software via and ODBC connection. The result is the SO No field is populated with a list of Sales Order No's as they exist within the ERP software. Within the Technical Form I have also the Customer Name.

This is where the problems begin. Within the ERP software we have two tables which I need to use: SO_Header and Customer. The SO_Header contains all the Sales Order info and the Customer table contains all the customer info. What I need to do is populate the customer name field in by db with the relevant customer based on the SO No chossen. However the Customer Name does not exist in the SO_Header table. Instead both the SO_Header table and the Customer tables contain a field called Cust_Code.

Now if I wanted to link these in Crystal Reports I would link from the SO_Header table to the Customer table using the Cust_code field. This would give me the corresponding Customer name based on the Sales Order No.

How can I get the correct Customer Name to appear automatically in my Access form based on a SO No chossen by the user?

I would realy appreciate any help on this! If you need me to clarify anything please ask.

Thanks in advance!

RobV

View 1 Replies View Related

Tables :: ODBC Linked Table Showing Only MAX Date

Jan 22, 2014

Using access 2007 (and tried 2003) I have a linked ODBC table which is using an ORACLE driver. If I query the table directly through ORACLE/SQL for a particular 'job number' I get the following results:

18598979 3 06-DEC-13 10:10
18598979 0 03-DEC-13 10:34
18598979 1 03-DEC-13 10:34
18598979 2 06-DEC-13 08:20

However when I link the table through access and view the same job number within the table (not even using a query) it displays the following:

18598979 3 06/12/2013 10:10:00
18598979 3 06/12/2013 10:10:00
18598979 3 06/12/2013 10:10:00
18598979 3 06/12/2013 10:10:00

As you can see it only displays the MAX date/time and associated data from the 4 available records, duplicating the record. I've checked the field formats and they all look fine. It does the same if I pull through the table on it's own or with other linked tables.

View 2 Replies View Related

Changing ODBC Linked Table Stored Credentials

Jul 24, 2013

I have a number of databases that use ODBC connections for working with linked tables from a SQL database. The source SQL data has been moved to a new server with new sa credentials. I have updated my local ODBC connections to use this new information. However, when I added numerous tables to the databases I checked the box to save the password so that it wouldn't prompt me or other users every time I wanted to use the Access database. Is there a way in Access to change this stored password? So far I haven't been able to find one.

View 1 Replies View Related

Linked Table Manager Doesn't Relink ODBC Tables.

Sep 15, 2006

Hello,

The scenario. Two PC's, one older than the other, both running Windows 2000 (SP4) and Office 2000 (SP3). I use Access as a front end to a MySQL database, connections are made using ODBC.
I recently altered the structure of a table and attempted to re-link the table in access using the Linked Table Manager on the new PC. All that happened was the hourglass came on and never went away. I tried to do this on the old PC and it worked fine first time.

Any ideas why I can't get it to work on my new PC? It's quite important as I won't have the old PC with me much longer :-(

Regards,

Matt.

View 3 Replies View Related

Saving A Linked Table Locally

Jun 1, 2005

Hello,
I just got set up with access to a bunch of linked tables with information from our catalog--I work at a library. Most of the library will be using this to run reports, mostly premade reports. I, however, want to take the information from the tables to fill in the gaps in my own access database. I really couldn't care less about their premade reports.

I have been unable, however, to figure out how to save those linked tables locally. Because they're linked (with the green globe icon), I can't delete the records I don't want. I want to delete them because otherwise the file includes all of our books--I only want our journals--a MUCH smaller number. I have tried copying the table and pasting it, but it simply pastes another linked table. I also tried to copy the structure of the table only thinking I could cut and paste the records I wanted into the new table, but that didn't work either--the full data is still there.

I wrote our IT people, and I got a response suggesting that I not delete anything, because deleting records would mean the reports I run would be inaccurate. That is exceedingly frustrating, because I've talked to him several times about my wanting to just take the information to make my own database complete. I don't care about running reports. I don't need to run their reports for my job--and when I do need reports, I make them myself from my local files.

The reason I need to have my own local copy is because I need to match up fields with my local database, and the fields need some minor editing first in order to match up. A simple matter--if only I could get a local copy.

So, does anyone know how to do this?

Thanks!

-Siena

View 2 Replies View Related

Linked ODBC Tables

Nov 12, 2007

My problem is best explained by example;

In Access Database A, I have some linked ODBC tables (to an SQL Server database) and some local tables.

In Access Database B, I have a front end which links to all the tables in Access Database A.

The problem is, when I try to link the tables in B, I cannot see the ODBC tables linked in A. Is there any reason why not? 'Why not link Access Database B to the SQL Server tables directly?', you may ask. Well, there is an answer to that but it is quite difficult to explain. I assure you there is one. :p

View 2 Replies View Related

Changing Table Source In Query Using Vb Instead Of Manually Doing It.

Aug 17, 2004

I have a Query called Median and all it does is pulls all the fields and all the records from 1 table. Since the Query is connected to a lot of other queries, and the table it's connected to changes twice a month, I was wondering if there was a way to set this up in a form maybe using vb, so a user could maybe Click a new table from a combo box list of current tables in the database, then click a button that says "Change", and it change all the fields to the new table and removes the old table from the query.

Or what might be better is: Already on the form, i have part of the name of the new table already entered by the user (it bases the new name of the new table off this text box). So, what I can use is the Text in that box and an & " the rest of the tablename", and automatically change the table in the Median Query based on that criteria. If that is too complicated, then a combo box will be sufficient.

Another way i guess, would be to setup a hidden field that takes whatever's in my text box (which is a date) and adds " the rest of the tablename", then I could base the replacement table of the Median on that one text box.

Anyway, is there a way to do this?

View 6 Replies View Related

Linked Tables And Multiple ODBC Connections

May 18, 2005

Hi!

I'll cut right down to it: I have an ODBC connection to a (SQL Server) test database on my development machine that is identical to the live database to which a (different) ODBC connection exists on the PC where this Access application is to be deployed. The problem is that when I deploy, there are some tables that have to be updates, because they still point to my test database. The other tables look like they use the default database for that user.

I used to be able to just copy the Access application and it would work without having to update any linked tables.

To clarify, the tables that work without a problem look like this in the Linked Table Manager:

t_mytable (DSN=odbc;)

And the problem tables have this:

t_mytable2 (DSN=odbc;DATABASE=my_test_db)

I don't seem to be able to clear that DATABASE= thing up. Can anybody tell me how this works?

If this question is overly trivial, I apologize.

View 1 Replies View Related

Insert The Records Into ODBC Linked Tables

May 25, 2007

Hi,

I have a lot data to append to ODBC linked table in MS Access. I want to know that which way is faster to append the records.

if I append the data into ODBC linked table,
1) create the one query (append) to insert the records into ODBC linked table
2) use the VBA code (DAO/ADO) to insert the records into ODBC linked table

which way is rather faster?

View 1 Replies View Related

Error Handling For ODBC Linked Tables

Jan 17, 2006

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


Thanks in advance for your help :D

View 14 Replies View Related

General :: ODBC Linked Tables Losses Connection

Jun 13, 2012

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 ?

View 3 Replies View Related

Combo Box Based On Linked Table Needs To Pull Info For Sub-Form From Non Linked Table

Aug 31, 2007

I am trying to build a Form that will show an estimate (then eventually will be moved to a project if customer and employee aggree to price and project) in a Form F_Estimates is a M_Customers(Customer_ID) (Based on a Table) and thier info in a Subform. Also is the "projected costs" from parts out of the Parts(Part_ID) (Based on another Table) in a second Subform as a list that I need to calculate $$$ in
(Dang that still sounds evil and definately NOT understandable even after edit... so)

Here's some basic info

Tables

EstimatesandParts - Table
EstimatesandParts_ID : Autonumber
Estimate_ID : Number
Part_ID : Number

Parts - Table
Part_ID : Autonumber
PartNumber : Text (not a number due to some part#s have letters in them)
PartName : Text
Unit Price : Currency
Description : Text

Estimates - Table
Estimate_ID : Autonumber
InvoiceNumber : Text (again can have letters in it)
EstimateDate : Date/Time
EstimateTime : Date/Time
Employee_ID : Number
Customer_ID : Number
ProblemDescription : Memo

Customers - Table
Customer_ID : Autonumber
FirstName : Text
LastName : Text
CompanyName : Text
Address : Text
City : Text
Province_State : Text
Postal_ZIPCode : Text (CDN Postal codes are letter num letter...)

you can see the link table in the EstimatesandParts Table

Now I want to use that link to populate a subform in the F_Estimates form

Forms

SF_Customers - SubForm

(all boxes atm are text boxes on this form till I figure out the Parts section then will use same base for this so I can pick any customer in the database to be the customer for this estimate. Also will have ctrl button for making new customer with customer form and a refresh on Focus Gain bit of code)

FirstName
LastName
CompanyName
Address
City
Province_State
Postal_ZIPCode

SF_Parts - SubForm
Default View -Continuous Forms

(want it to be a list of parts that I can grab prices and descriptions from then in a bit of code to calculate a cost of parts)

Part_ID : Combo Box
Control Source - Part_ID
Row Source Type - Table/Query
Row Source - SELECT Parts.Part_ID, Parts.PartNumber, Parts.PartName, Parts.UnitPrice, Parts.Description FROM Parts ORDER BY Parts.Description;

(Pulls info from the table Parts for input into a list of parts to be used on that project)

PartName : Text Box
UnitPrice : Text Box

(here's where I run into problems due to the fact that the form is not based on the parts table but rather the link table EstimatesandParts so I can't propogate the info to the 2 other text boxes, ps I dont care if they cant be text boxes and have to be linked or some other type I'm not "set" just need to find out how to make it work )

(have tried a couple things to complete this task)

Me.txtPartName = Me.Part_ID.Column(2)
Me.txtUnitPrice = Me.Part_ID.Column(3)

(works AWSOME ... for ONE ROW then propogates the second selection to the first and second and third selection to first second and third and so on ...)

(tried to make control source for the txtPartName to)

=Forms!Parts!Partname

(Doesnt exist .. akkk, cant use ActiveForm either as it doesn't focus on the SubForm but the MainForm ... cry)

F_Estimates - Form

Estimate_ID
InvioceNumber
EstimateDate
EstimateTime
ProblemDescription

(all basic Text Boxes)

Employee_ID
Customer_ID

(Combo Boxes Select Customer and Employee from list of present ones of each)

SF_Customers
SF_Parts

(Both SubForms on the main form)

Now this is an Exerp from my entire Database I like to work on one small problem at a time and I have made this its own little database till I figure out the problem then I will bring the info I learn back into the rest of the database and go from there ...

Hope you can help I have a feeling I will need to make a recordset and go from there but I'm just not able to wrap my head around that for some reason

Thanks in advance for ANY and ALL help that I get from here

View 10 Replies View Related

Tables :: Automatic Refresh Of Linked Tables - ODBC Connection

Jul 22, 2015

I have linked tables from SQL Server using ODBC connection that their location never changes. I have used certain fields of those tables to create queries and make table queries to derive to the information I needed.

On these tables on SQL Server, there is new data added daily. Every day, midnight, there's new data records added of whatever transactions took place in that working day. how often do I need to refresh linked tables in this case to get the latest data added. I mean, once I am linked, the make table query using those defined fields, would it get the latest data added by default when the query is executed, or I must refresh linked tables using Linked Table Manager and then run make table query.

Also, if I want the access to automatically refresh linked tables, can I use the following code? I have added this code, and executing it through a button, but I don't see anything happen, the database becomes inactive for couple seconds (I guess while it is updating) but I don't know is it updating the tables for sure or not, though I am not receiving any error when executing the code through the button.

Function RefreshLinkedTables()
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.RefreshLink
End If
Next td
End Function

View 2 Replies View Related

Tables :: Update ODBC Linked Tables On MS Access?

Apr 30, 2015

How to Update ODBC linked tables on ms access?

View 5 Replies View Related

Manually Changing The Order Of Records

Mar 20, 2005

I have records which represent tasks and the tasks need to be scheduled. They can be ordered to some degree by sorting the table on specific fields and then by sorting parts of the table by specific fields, however, the final ordering needs to be done manually as it cannot be done by a field sort. So, how can you move records around manually? It would be like a CUT and PASTE INSERT. Thanks.... Lester

View 3 Replies View Related

Manually Entering Data To A Record

Sep 15, 2005

Hi,

I am new to access programming. I want to do the following but don't know how :-

I have a form which is full of text boxes for people to enter data. I want them to enter the relevant data into those textbox's and then to click a SAVE button. Only when the SAVE button is pressed do I want the contents of the text boxes to go into the relevant fields in a table, i.e. they are all unbound.

Can anyone tell me how this is done please and possibly give an example code?

Thanks

View 4 Replies View Related

Forms :: How To Manually Make New Tabs To Appear On Second Row

Jun 30, 2014

How can I manually make new tabs appear on the second row when they normally would not?

I want to force some tabs onto the second row of a multirow tab control...

View 2 Replies View Related

A Solution To Manually Processing 'Entry Forms'

Mar 2, 2007

Hi Guys,

I've been pondering over a problem for a couple of weeks now - We receive around 1000 paper entries to our competition, and these all need manually entered into the access database in a one-er.

Is anyone aware of any ideas of how this could be made easier, and more automated?

Thanks.

View 3 Replies View Related

Manually Changing Displayed Record On A Form

Mar 7, 2006

I have a form that is linked to a table. tblContacts

On the form I have added a listbox which has every possible Contact (Name) listed. I wanted to be able to click on a contact name and then have the form bring up all the information related to the contact.

right now i have on the click of the listbox a msgbox that gives me the contact id associated with the contact name.

BTW, this is a project i am taking over from someone. If it was my choice I would be creating a web app.

View 2 Replies View Related

Tables :: Calculated Field OR Manually Entered Value?

Jul 14, 2014

I have a calculated field that is a total based on 5 other fields. These fields are rankings of priority on individual categories, while the calculated field is a total priority ranking for the entire record. (A ticket) The 5 categories are prioritized, and the calculated field runs it through our equation to determine the overall priority of each ticket.

Now, some tickets don't have individual priority numbers in their categories. Therefore, no total priority number is calculated in the calculated field. We would like these tickets to be given a manual total priority, but we can't manually enter numbers into the calculated field to do this.

Is there a way of saying this to the calculated field... "Use the equation to run the individual categories to come up with your total. IF there are no numbers in those categories, reference the field 'Manual Priority' to find your number."

This way, we'd like to keep all of the total priorities in the calculated field. It's just that some of them have to be manually entered because there will be no individual priority categories to calculate the total by.

The current formula for this total priority field is...

(([Size of Financial Risk?]*20)+([Impact on Internal Customers]*15)+([Non-Financial Impact]*20)+([Impact on External Customers]*30)+([Estimated # of Clients Impacted]*15))

I'd like to say, "Do that... or use this manual field if that equation turns out to be nothing."

View 8 Replies View Related

Average Calculation Automatically Not Manually Manipulated

Oct 16, 2013

Calculating an average based on 12 fields. The fields are the months (Jan-Dec) I want the blood average of the year. Depending on the result an employee might only have to provide a sample 4 times a year or quarterly.

If their result is greater than 15 a monthly sample must be provided. The employees on the quarterly draws only will have 4 samples to average whereas a monthly draw can have 12 or fewer. I am not getting a correct answer using /12 as there are 0 in the fields and the right answer would be on samples >0

I am attaching a mini mdb ...

View 4 Replies View Related

Copying Table From ODBC Datasource

Nov 1, 2005

Hello,
Is it possible for a [MSAccess] query to obtain table data from an [external] ODBC Datasource?
Something like:
INSERT INTO TableA SELECT * FROM ODBC;DSN=MyDSN;UID=;PWD=;.TableA

I found a similar thread but didn't understand the answer which mentioned linking a back-end to the Access Table.

Cheers.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved