|
Subject:
|
Can a Make Table Query produce a Linked table?
|
|
Posted By:
|
kronik
|
Post Date:
|
5/12/2006 1:33:26 PM
|
If so, how?
I have a table that I would like to be linked every time the query is run..
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
5/12/2006 2:30:16 PM
|
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
|
|
Reply By:
|
kronik
|
Reply Date:
|
5/12/2006 4:35:46 PM
|
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.
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
5/15/2006 6:31:57 AM
|
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
|
|
Reply By:
|
kronik
|
Reply Date:
|
5/15/2006 2:29:28 PM
|
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... 
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
5/16/2006 6:17:31 AM
|
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
|
