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 Replies
ADVERTISEMENT
Jun 27, 2005
Hi there,
I am about to start working on a new project where I have a front-end in Access2000 that is linked to a ODBC Data Source (ORACLE).
There could be some performance issues in the future, when the users have to retrieve (query) the data from the ODBC over the network and it slows down. Nothing is sure yet, but when this is the problem, I will need to look at an alternative plan. I had the following in mind:
I would like to create a scheduler program, that will retrieve all the relevant data from the ODBC and update the tables that are local to each user’s Access .mdb file. Can I do something like this in VBA, or do I need to do something with the Windows Scheduler?
Any tips are welcome, thanks in advance
J
View 2 Replies
View Related
Jan 13, 2014
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.
View 1 Replies
View Related
Nov 21, 2013
I'm having trouble with a new project I'm working on. The application is mainly going to be used to display data, which comes from a linked table. It has to be a linked table (in my opinion) because it's replaced once per week from a fresh data dump. For each of those records, though, there will be notes made in a local table named "Custom-Data". My trouble is displaying a mix of information from the linked table, "Roster", and "Custom-Data" because linked tables can't be assigned a primary key.
Essentially, when a record is pulled up, a bunch of data from "Roster" will be shown in addition to the comments from "Custom-Data".
View 2 Replies
View Related
Apr 1, 2015
I have a database that is designed with a front and back end, with the FE linked to the BE. Some potential customers want to play around with the database, and the easiest way for me to get them access to it would be for me to combine the FE and BE and just give them a copy of that. how to link a table, and I know how to import a table. What I'm unsure of is how to cleanly convert a linked table to a local table.
View 2 Replies
View Related
Jul 30, 2006
Well, heres the situation. I have a complicated query that refuses to work all the time using a linked table for the data (data is gathered from a FoxPro DB). If I copy the data into a local table in my database then the query will run fine.
The data needs to be updated only 1/month but I don't want to have to do it manually every month. I would like to use VBA to copy and paste the data from the linked table into my local table. Does anyone know of an efficient way to do this? I'm trying to avoid running a VBA loop and adding each record one-by-one (very slow).
View 2 Replies
View Related
Sep 12, 2013
I have Linked a few ODBC Databases to my database in the Tables objects. How do I make them usable for queries or as record source for my form? I tried to create a query but the ODBC databases do not show up in the Table options.
View 14 Replies
View Related
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 4 Replies
View Related
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
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
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
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
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
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
Jan 17, 2007
Hi all,
ive set up an access db which is linked to another access db- i need to be able to filter by form on the table linked to the othera ccess db- it allows me to on the original but not on the linked table giving me:
is null
is not null
as the options-
is this possible?
if so what is going wrong?
cheers
dubs
View 1 Replies
View Related
May 10, 2013
I have a form that is based on a linked table of students eligible to complete a online survey. Within that form, I have a sub form (based on another linked table) of students who have completed the form and at what date and time.
Only about 1 in 10 eligible students complete the form.
I'd like to filter by students who have completed the form, and sort by date submitted.
So I have the main form called "students" and the subform with the date submitted is called "New - BasicInfo subform" and they are linked by student ID. The date submitted field in the subform is formatted like "5/6/2013 3:07:32 PM".
I've tried adding this to me "filter" field in the STudents form properties:
Between [Students]![New - BasicInfo subform]![05/06/2013] AND [Students]![New - BasicInfo subform]![10/01/2013]
No luck, I get syntax error missing operator.
View 1 Replies
View Related
Nov 19, 2014
I am trying to make a stand-alone version of a database whose files are mysql files connected by odbc. When I copy the linked files and save them as local files, they come in with the auto-numbered id field still marked as the primary key, but as just "number" and not auto-number. It does come in marked required. I can't change the type to auto-number, so it errors when the user tries to add a record using the input form. Also, I have the fields used to keep true/false data set up in the mysql files as integers with default values of 0. When they are saved as local tables, they come in marked required but without a default value. Hence, when a user enters a new record in a form that doesn't use that particular check box, it errors. What am I missing? Is there an easy way around this?
View 1 Replies
View Related
Jan 25, 2005
I have a SQL table that holds financial data. There are 12 columns labled Acct_Per01 through Acct_Per12. I need to select specific GL codes and sum the values in these columns as various groupings, Private, Commercial, Ancillary etc. for each of 14 hospitals.
How can I best create a working table where I have one record for each hospital for each fiscal period.
I want the final table structure be be like the sample below.
Any thoughts on the best way to do this?
Fiscal_Period 200506
Hospital ALX
Private 79,500
Commercial 0
Ancillary 650,457
View 4 Replies
View Related
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
Oct 16, 2007
Hi all!
I have a bit of an issue. Im trying to retrieve a row from SQL and insert it into my local DB table.
However, when I try and execute the code, I get an error message. The data had not been inserted into my local DB table.
Here is the code:
strsql = "INSERT INTO EntryDetail_Local SELECT EntryDetails.* FROM EntryDetails where entrykeyid = " & plngentrykeyid & ";"
Set qdftemp = mydb.QueryDefs("qry_Append_EntryDetail_Local")
qdftemp.SQL = strsql
qdftemp.Close
qdftemp.Execute
DoEvents
Any ideas?
Cheers
View 2 Replies
View Related
Mar 12, 2008
I have a form based on a query where I want to document the status of certain records. The original data is on a SQL server so I am linking to it in my query. I have a local table with the added fields to document my review and status. My query has both tables in it and are linked by the report number. So I have join properties to show all records from the SQL database and only the records in the local table that match. So initially the data in my local table will be blank. I want to use the form to add comments on the status. The problem is, when I type in the form fields nothing happens. It's like the query is confused and won't let the form write back to the local table.
So my question is, do I have my query set up properly so that I can read the fields from the SQL database and yet write in my comments to the local table. I am using a select query.
Thanks for the help.
Jim
View 1 Replies
View Related
Feb 18, 2008
Hi Guys,
This is one of this only times i've had a problem whose answer i couldn't find on this or other forums, so here's the question.
My DBase links to the back end tables
tblBrkdn
tblBrkdnArchive
tblBrkdwnTradespeople
tblBrkdwnTradespeopleArchive
The following two tables are local, not linked
tblBrkdnArchiveTemp
tblBrkdwnTradespeopleArchiveTemp
After running through the following code, all of the four above mentioned linked tables are now local. It's vexing. I'm terribly vexed. If you can help, my status would chnge from vexed to joyous....:
Thx in advance...
' 1) Join all together (Each Table)
' Create two recordsets, One for the Brkdn set, one for the Tradespeople Set.
' Brkdn: Want it to be the Union of all records in (tblBrkdn, tblBrkdnArchive) without duplicating BrkdwnID
'Standard Union Query with no duplicates. Transferring data into a temporary table
SQLStr = "SELECT * INTO tblBrkdnArchiveTemp FROM ("
SQLStr = SQLStr & "SELECT tblBrkdn.* FROM tblBrkdn INNER JOIN tblBrkdnArchive ON tblBrkdn.BrkdwnID = tblBrkdnArchive.BrkdwnID "
SQLStr = SQLStr & "Union ALL "
SQLStr = SQLStr & "SELECT tblBrkdn.* "
SQLStr = SQLStr & "FROM tblBrkdn LEFT JOIN tblBrkdnArchive ON tblBrkdn.BrkdwnID = tblBrkdnArchive.BrkdwnID "
SQLStr = SQLStr & "WHERE (((tblBrkdnArchive.BrkdwnID) Is Null))"
SQLStr = SQLStr & "UNION ALL SELECT tblBrkdnArchive.* "
SQLStr = SQLStr & "FROM tblBrkdn RIGHT JOIN tblBrkdnArchive ON tblBrkdn.BrkdwnID = tblBrkdnArchive.BrkdwnID "
SQLStr = SQLStr & "WHERE tblBrkdn.BrkdwnID is null);"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True
' BrkdnTradespeople: Want it to be the Union of all records in (tblBrkdwnTradespeople, tblBrkdwnTradespeopleArchive) without duplicating ID
'Standard Union Query with no duplicates. Transferring data into a temporary table
SQLStr = "SELECT * INTO tblBrkdwnTradespeopleArchiveTemp FROM ("
SQLStr = SQLStr & "SELECT tblBrkdwnTradespeople.* FROM tblBrkdwnTradespeople INNER JOIN tblBrkdwnTradespeopleArchive ON tblBrkdwnTradespeople.ID = tblBrkdwnTradespeopleArchive.ID "
SQLStr = SQLStr & "Union ALL "
SQLStr = SQLStr & "SELECT tblBrkdwnTradespeople.* "
SQLStr = SQLStr & "FROM tblBrkdwnTradespeople LEFT JOIN tblBrkdwnTradespeopleArchive ON tblBrkdwnTradespeople.ID = tblBrkdwnTradespeopleArchive.ID "
SQLStr = SQLStr & "WHERE (((tblBrkdwnTradespeopleArchive.ID) Is Null))"
SQLStr = SQLStr & "UNION ALL SELECT tblBrkdwnTradespeopleArchive.* "
SQLStr = SQLStr & "FROM tblBrkdwnTradespeople RIGHT JOIN tblBrkdwnTradespeopleArchive ON tblBrkdwnTradespeople.ID = tblBrkdwnTradespeopleArchive.ID "
SQLStr = SQLStr & "WHERE tblBrkdwnTradespeople.ID is null);"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True
'delete everything from the four non-temporary tables
'tblBrkdn
SQLStr = "DELETE * FROM tblBrkdn"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True
'tblBrkdwnTradespeople
SQLStr = "DELETE * FROM tblBrkdwnTradespeople"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True
'tblBrkdnArchive
SQLStr = "DELETE * FROM tblBrkdnArchive"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True
'tblBrkdwnTradespeopleArchive
SQLStr = "DELETE * FROM tblBrkdwnTradespeopleArchive"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True
' 2) Paste the entire record set to each Brkdn table
'tblBrkdn
SQLStr = "SELECT * INTO tblBrkdn FROM tblBrkdnArchiveTemp"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True
'tblBrkdnArchive
SQLStr = "SELECT * INTO tblBrkdnArchive FROM tblBrkdnArchiveTemp"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True
' 3) Delete from Active & Archive Brkdn tables WHERE [DATE] < txtArchiveDate.Text
'tblBrkdn
SQLStr = "DELETE * FROM tblBrkdn WHERE ([DATE]<#" & dateStr & "#);"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True
'tblBrkdnArchive
SQLStr = "DELETE * FROM tblBrkdnArchive WHERE ([DATE]>=#" & dateStr & "#);"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True
' 4) Paste to Active Tradespeople table WHERE Associated Breakdown Record [DATE] >= txtArchiveBeforeDate.text
SQLStr = "SELECT tblBrkdwnTradespeopleArchiveTemp.* INTO tblBrkdwnTradespeople FROM tblBrkdwnTradespeopleArchiveTemp INNER JOIN tblBrkdn ON (tblBrkdwnTradespeopleArchiveTemp.BrkdwnID=tblBrkd n.BrkdwnID)"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True
' 5) Paste to Archive Tradespeople table WHERE Associated Breakdown Record [DATE] < txtArchiveBeforeDate.text
SQLStr = "SELECT tblBrkdwnTradespeopleArchiveTemp.* INTO tblBrkdwnTradespeopleArchive FROM tblBrkdwnTradespeopleArchiveTemp INNER JOIN tblBrkdnArchive ON (tblBrkdwnTradespeopleArchiveTemp.BrkdwnID=tblBrkd nArchive.BrkdwnID)"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True
' 6) Clean up: Delete all records from the temporary tables
'tblBrkdnArchiveTemp
SQLStr = "DELETE * FROM tblBrkdnArchiveTemp;"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True
'tblBrkdwnTradespeopleArchiveTemp
SQLStr = "DELETE * FROM tblBrkdwnTradespeopleArchiveTemp;"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStr
DoCmd.SetWarnings True
View 2 Replies
View Related
Jul 1, 2015
I have the following script that either converts a single linked table to a local table or ALL linked tables to local tables, however I am getting the following 2 error messages on several tables for the ALL tables script.
error 3709, the search key was not found in any record
Error 3300, cannot create a relationship.
Code:
Sub Convert_Linked_Tables_To_Local()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb
GoTo Multi1
[code]....
View 5 Replies
View Related
Oct 31, 2013
Adding a field in Access 2007.
I am adding a exisiting field which is already available on a Global Table and would like to add it on a local table within the same database. Also bearing in mind the db contains main objects - Tables/Queries/Sharepoint lists/forms/reports
Whats the process in doing this? Once added how does the data get populated?
View 1 Replies
View Related
Apr 27, 2007
I'm curious about everyone's opinion of creating an Options table to keep track of things like defaults vs. using the system registry.
Example: For a montly report, I'd like to remember the previous end-date the user picked and use previous end-date+1 as the starting date the next time the user runs the report to make it easier on the user.
Thoughts?
thanks
Lution
View 2 Replies
View Related
Jul 27, 2006
I’m really new to access, learned through video professor. I have access 2000 Question: my main table contains basic client info. There is a yes/no field on it. When I click on the box in the form I would like to link it up with another table that I will create with some of the fields already filled out (name, Phone…) from the first table.
Example: the y/n box is labeled as “Buyer” if I click yes in the form; I want to create another table that asks more questions about the ones who are buyers only. In the second table, I also want a few of the fields to fill in automatically with some of the info from the first table (ID#, name, phone#...).
thanks
View 1 Replies
View Related
