Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 12th, 2006, 01:33 PM
Registered User
 
Join Date: May 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Can a Make Table Query produce a Linked table?

If so, how?

I have a table that I would like to be linked every time the query is run..

  #2 (permalink)  
Old May 12th, 2006, 02:30 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You don't need to actually link the table. You can make the proper join in subsequent queries of the table, and this will provide referential integrity.

Does this help?


mmcdonal
  #3 (permalink)  
Old May 12th, 2006, 04:35 PM
Registered User
 
Join Date: May 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Not particularly because I'm not too well versed in Access, not to mention the linking I'm referring to is linked to a Sharepoint list.

  #4 (permalink)  
Old May 15th, 2006, 06:31 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You will have to write code to go out and get the list data in a recordset. You can then query the recordset. Alternatively, you can put parameters on the recordset results.

With Sharepoint lists, you can go out to the SQL server back end, or MSDE back end, and get the data just like you were getting it from a regular SQL server. You can also create a view on the SQL server and pull from the view instead of the list table if you are going to pull the same sort of data each time. The view will optimize the query beforehand and it will run faster. Then just pass parameters to the view.

Depending on how the database is used, you may also make a link to the SQL server list table and then just do local queries. It depends on how much data there is and how hot the wire will be. Many sharepoint list tables are very very tiny compared to the average SQL table. I have small ones with more than 100,000 records, and sometimes I link directly to them or dump the whole table into an Access database when I open the database, and then do all my queries locally. You may want to do this. Use DoCmd.TransferDatabase.

Can you do this or do you need more help? The fact that it is in sharepoint will not effect the coding too much.

mmcdonal
  #5 (permalink)  
Old May 15th, 2006, 02:29 PM
Registered User
 
Join Date: May 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by mmcdonal
 You will have to write code to go out and get the list data in a recordset. You can then query the recordset. Alternatively, you can put parameters on the recordset results.

With Sharepoint lists, you can go out to the SQL server back end, or MSDE back end, and get the data just like you were getting it from a regular SQL server. You can also create a view on the SQL server and pull from the view instead of the list table if you are going to pull the same sort of data each time. The view will optimize the query beforehand and it will run faster. Then just pass parameters to the view.

Depending on how the database is used, you may also make a link to the SQL server list table and then just do local queries. It depends on how much data there is and how hot the wire will be. Many sharepoint list tables are very very tiny compared to the average SQL table. I have small ones with more than 100,000 records, and sometimes I link directly to them or dump the whole table into an Access database when I open the database, and then do all my queries locally. You may want to do this. Use DoCmd.TransferDatabase.

Can you do this or do you need more help? The fact that it is in sharepoint will not effect the coding too much.

mmcdonal
Can I do it? No, honestly... :(

  #6 (permalink)  
Old May 16th, 2006, 06:17 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

If you can give me more details of the SQL server and how you want to query it, we can set something up. I would need to know the list table name, the list table fields and field types, connection information, etc. You may want to email me directly.

How much do you know about creating connections, creating recordsets, cursors, locks, queries (SQL ANSI 89 - 92) etc?

Anyway, this is all very simple once you know how. Most of the posters here do this sort of thing all day long.

HTH

mmcdonal


Similar Threads
Thread Thread Starter Forum Replies Last Post
Make a linked table in VBA Admiral_Hook Access 13 November 22nd, 2012 02:15 AM
Update Query using inner join with a linked table ceekay Access 3 June 8th, 2007 12:35 PM
Make Table Query CongoGrey Access 2 June 1st, 2007 06:22 AM
Make Table query : table in Another Database marnik Access 1 March 19th, 2005 12:39 PM
Error on Make-Table Query In Union Query rylemer Access 1 August 20th, 2003 07:42 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.