Access 2000 Link to SQL Server db tables

I have a Access 2000 database that has approximately 100 tables.  I would 
like to link the Access tables to SQL Server database tables to give better 
access to the report and forms in Access.  Please let me know the best way to 
link Access tables to SQL Server database tables.

2/28/2008 8:36:03 PM
access 16762 articles. 3 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 17

On Thu, 28 Feb 2008 12:36:03 -0800, Joe K. <Joe>

>I have a Access 2000 database that has approximately 100 tables.  I would 
>like to link the Access tables to SQL Server database tables to give better 
>access to the report and forms in Access.  Please let me know the best way to 
>link Access tables to SQL Server database tables.

Ummmm.... what kind of "link" do you have in mind?

You can use File... Get External Data... Link to connect your Access frontend
(forms, queries, reports, etc.) to an existing ODBC connection to a SQL/Server
database. You may need some tweaking of the frontend, JET (the Access builtin
database engine) and SQL are somewhat different.

If you mean you want to link local Access tables to other SQL tables, please
explain the nature of the "link".
             John W. Vinson [MVP]
2/28/2008 11:07:12 PM

Similar Artilces:

Linking ActiveX Controls
I want to have a checkbox or two, that only become active or available for selection when a specific option button is selected. Can / how do I do this? I guessed that these controls were on a worksheet. I went into designmode and double clicked on one of the optionbuttons and pasted this code: Option Explicit Private Sub OptionButton1_Change() Me.CheckBox1.Enabled = CBool(Me.OptionButton1.Value) Me.CheckBox2.Enabled = CBool(Me.OptionButton1.Value) End Sub MikeyS wrote: > > I want to have a checkbox or two, that only become active or available for > selection when a s...

Export an Access Table as an dbase III file
I was thinking this should be simple. I want to copy, transfer or whatever the TBS Table in the VPS_TBS.mdb database and send it to our shared drive, but have the file be a .dbf file. I tried the F1 Help DoCmd.TransferDatabase acExport, "DBASE III", ' "C:\@CDATA\VP_tbs.mdb", acTable, "tbs", _ ' "K:\CPANALYSIS\BusinessMgt\VP\Access\DBASE\tbs1.dbf" I've tried this in a Macro, and in code, but ways fails. The error tells me the path doesn't exist, but that is the path. Does anyone see any obvious errors. If you kn...

Do not modify the table???
Looking for data flow diagram of GP 9.0 SQL tables I found this: 3. Do not modify the table – sometimes it seems to be nice if you just append couple of extra columns to the table - like in IV00101 - inventory master file why wouldn't you just add couple of additional descriptions. If you do this - Great Plains Dexterity engine will fail reading all your items - due to the fact that DYNAMICS.DIC (main Great Plains Dictionary file) has exact description of all the tables and Dexterity uses it for reading and writing into the specific table

crash when right mouse click in table properties
When I go to tables, lay out mode, properties and click with the right mouse button, Access crashes. Same happens in the form properties. Content of the error report: AppName: msaccess.exe AppVer: 12.0.4518.1014 AppStamp:4542815c ModName: msaccess.exe ModVer: 12.0.4518.1014 ModStamp:4542815c My database was developped in Access 2000 (english version), on a Dutch XP - SP2. It was working very well. I had to shift it to a French XP, SP2 , running as an .mdb in a French Access 2007. Since then it is very unstable. Any suggestions? Michel P. does anybody has a solution for ...

Slow Access 97 since moving to new 2003 Server
We have an access 97 MDB that we had on an NT4.0 server. We have about 30 wrksts that have mdb front ends loaded locally to access the MDB backend. All was great until this evening when we decided to ditch the 9 yr old NT 4 server and replace it with a Dell Dual Xeon 2003 R2 server. All data copied form the old server to the new server was perfect. Performance increases were everywhere when we talk about network thru put. However, once we try to get the workstations in to the database on the new server, performance leaves and QUICK !. To compare, the old server/workstation combo would ta...

owa on cluster server loading error.
hello people, got a problem with owa. the problem is that I can logon to owa and see the menu on the left and top of the screen. only the content frame stays empty with "loading...." in the top. this happens in the locale network, on the server as well from remote locations. the system is an active/passive cluster. running 2000 advanced server with all service packs. running exchange 2003 enterprise with service pack 2. already remade the cluster http resource without succes. also checked the microsoft wesite. all without luck. any ideas? .... do you see the same behaviour when...

What to monitor in MS Exchange 2000
I was wondering what sort of things you Exchange experts out there might recommend I should be monitoring on my Exchange 2000 in the way of the performance logs and alerts, event viewer, ram usage, etc. anything I should be checking regularly to get my baseline right and keep me aware of any future problems. Any thoughts or feedback would be greatly appreciated. Thanks, There are a few basic things to see: memory, processor and disk usage and grow network trafic memory peaks (never over 80% during several seconds) event viewer (allways) smtp traffic backup logs (restore must be a norma...

Vitual Servers?
Is there a 'best practice' or 'industry standard' for showing virtual servers on a Visio network diagram? I am putting together a diagram and want to show the physical and virtual environments, and how they interconnect, on the same page. Ian virtual servers within a microsoft context are still pretty new (although one could debate how this applies to a web load sharing scenario) to have a best practice defined. Eventually you will have to decide on how to play the physical topology against the logical design. I prefer to do the site design using layers because it can ge...

Access97 Workgroup, db password and dsnless connection
Hi Folks; I am working on an older system that uses an access97 db. I am trying to connect to the db using a dsnless connection and ADO. The database is part of a workgroup, so there is a .mdw in addition there is a database password. Is it possible to access this using ADO and a dsnless connection ? BTW, there are a coupld of other db's that are part of the same workgroup and I can access these using a dsnless connection without a problem. Thanks in advance. Access 97 only supported a tiny bit of ADO. It was Access 2000 that really included it. The very best connection string websit...

Link child fields & Link master fields
Link child fields Link master fields I'm use this code to link master form and sub form Me.subclient.LinkMasterFields = "Text6" Me.subclient.LinkChildFields = "text7" The code work correctly What I want I want to remove the link I'm use this code but not work Me.subclient.LinkMasterFields = "" Me.subclient.LinkChildFields = "" What happen No record will display in the subform client Is there any way to display all records after remove the link ...

Access Webmail with Firewall in place
I have a netopia 9100 router that is acting as my firewall. Can I have clients use webmail without having to first VPN through the firewall? Thanks in advance. Rich ...

Linking Subforms in a Form
I'm usually pretty competant in Acccess. That is, I can find the answers on the web or in a book, but this has me stumped. I'm trying to build an application to view baseball stats. And what I am trying to do - but haven't found the method yet is the following: I've made a query to pull out a unique list of teams, and put that query in a form. I've made a query to pull out a set of hitting statistics, and put that query in a form. This hitting query has a relationship to the team query. Finally, I've trying to create a form, where I would insert both o...

Exchange 2000 issue
Running exchange 2000. We need to forward our email through our isp. I change the internet mail connector by the properties/general and entering the mail server name below the radio button. Restarted server, yet the emils are STILL going through the old host. Is there somewhere else I have to change this? Thanks in advance! Here is a good article on setting Smarthost for Exchange 2000, In addition, does the ISP know you are relaying off their SMTP Server? -- John Oliver, Jr MCSE, MCT, CCNA Exchange MVP 2007 Micr...

Recordset in Access 2007
I get Run-time error 13 "Type mismatch" on the Set rs line below: Dim rs as Recordset Dim sSQL as string sSQL = "Select LastName FROM tbl_Users" Set rs = currentdb.openrecordset(sSQL) Try: Dim rs AS DAO.Recordset instead of Dim rs AS Recordset Right now, you probably have the possibility to use either DAO recordsets, either ADO recordsets (with ADO higher in the priority list of references). Vanderghast, Access MVP "TADropik" <> wrote in message news:CEE47C66-4C8C-4511-8D3B-D32DFC620BB0@micr...

access denied
I try to copy and paste in the same folder my money data file but the message is "cannot copy <file name> .There is a sharing violation . The source or destination file may be in use". I close my application and I kill my process in task manager but nothin happened. Any ideas ? Thanks IBI Turn off Money Express in Tools|Options|Bills and Deposits and then exit Money??? "Ibi" <> wrote in message news:069901c37080$a68b55e0$a601280a@phx.gbl... > I try to copy and paste in the same folder my money data > file but the message is &qu...

Application becomes slow in windows server 2003
When i run my application(supports unicode) which i made in MFC(microsoft visual studio 6.0) running very slowly. While this application runs perfectly on winxp/win2000. My application open hard drive and read data from it. Please suggest how to improve the performance. Hello Ashish, Please use the MSDN forums for this, there you will find your programming experts, that maybe can have a look into your code. Best regards Meinolf Weber Disclaimer: This posting is provided "AS IS" with no warranties, and co...

Great Plains 9.0 Vs SQL Server 2005
Hi group Do I want to know a little but of compatibility of GP9.0 Vs SQL Server 2005? SQL Server 2005 are required by GP 9.0? GP 9 does not require SQL Server 2005. -- Charles Allen, MVP "Richard Padilla" wrote: > Hi group > > Do I want to know a little but of compatibility of GP9.0 Vs SQL Server 2005? > > SQL Server 2005 are required by GP 9.0? > > > > > but can I install Great plains with SQL Server 2005? "Charles Allen" <> wrote in message news:8EE2FBC5-975E-4B4B-9134-7736D04187D0@microsoft....

Word 2007 Table of Contents Automatic Update?
Does anyone know of a way to set an automatic update of a table of contents, say when you save or close a document? If so, please let me know. Thanks! Have you looked at Stefan Blom's reply to your previous duplicate question? -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA "tjb" <> wrote in message > Does anyone know of a way to set an automatic update of a table of > contents, > say when you save or c...

Pivot Table Row Question / Glitch?
Hello: I have a pivot with 5 columns in the row area. The last field is not aligning horizontally. The data is something like this: Area, Sub Area, Category, Current Desc, Proposed Solution. The proposed solution field is one row below, and one column to the right of the Current Desc field. Why isn't it on the same row as the current desc field. They are horizontally aligned in the datasheet, and these two fields are free text. Help and thanks, Germaine ...

How to restore data from old sql on a clean install of CRM
I've locked myself out of my orginal install of CRM instead of trying to fix the problem I made a clean install of my server and CRM. Now when I restore the MSCRM, Metabase, and MSCRMDistribution I get locked out the server because users do not exsist. How do I restore the database from the old sql and get it to work on my clean install of CRM. My main goal is to get back contacts and accounts. I can recreate the other stuff Hi Tino, Assuming you can still access the old sql db, you can make a clean installation of CRM. Then backup the old database, update these tables in the ...

Access 97 to Access 2000 11-03-03
After I converted the DB I'm receiving the compile error message:"Sub or function not defined ' SS_OnOpen Me'. Help!!! Hi LP, This is likely to be the result of something going wrong with the references in your VBA project. See On Mon, 3 Nov 2003 06:19:12 -0800, "LP" <> wrote: > After I converted the DB I'm receiving the compile error >message:"Sub or function not defined ' SS_OnOpen Me'. >Help!!! -- John Nurick [Microso...

External access to internal distribution lists and Mail Enabled Fo
I would like to have people from outside our organization to e-mail into one of our distribution groups. I can get to it internally without any trouble, but externally we are having some trouble with this. The e-mail group is called All Team Members and the e-mail is listed as When we send to this distribution list it is returned as undeliverable. Also, I am having the same problem on a mail enabled folder. Is the solution the same, or am I just missing something? Thanks in advance, Do you have any delivery restrictions on the DG? Nue "Tripp K" &l...

3022 error while run update SQL
I use CurrentDb.Execute strMYSQL, dbFailOnError to run my update query. I got run time error 3022 which is duplicate records found. I tried to update multi records from the code. I use following code to filter out the error, MS Access only updates the first record. Err_Handle: Select Case Err Case 3022 Resume Exit_Handle Case Else strErrMsg = strErrMsg & "Error #: " & Format$(Err.Number) & vbCrLf strErrMsg = strErrMsg & "Error Description: " & Err.Description Resume Exit_Handle ...

Pivot Table Custom Calculations #3
I have a pivot table with the "columns" set up as Budget - Actual - Variance for each month of the year. The rows are the expenses account, such as supplies, maintenance, etc. I want to be able to add the total of the "Actual" spent through September plus the "Budget" for October through December, basically to get a projected total by expense for the year. How can I do this? Thanks for your help! You could add a column to the source data, and calculate which amount should be used. Then add that field to the pivot table. For example, with dates in colum...

when an exchange server 2003 goes down
4 servers are running under Windows 2003 active directory. Exchange server 2003 is up and running without a glitch. We're nor using clusters. Almost everything is redundant (hardware and real time files replication from server to server) That told, I would like to add a level of redundancy to the exchange server in the event that something goes wrong. Can you point me out to something I could work on? thank you Thomas That really depends on your budget. For starters I would establish solid backup plan after doing a lot of research, test it a frequently, and do save those transact...