Can a Make Table Query produce a Linked table?

kronik · May 12th, 2006, 01:33 PM

If so, how?

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

mmcdonal · May 12th, 2006, 02:30 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

kronik · May 12th, 2006, 04:35 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.

mmcdonal · May 15th, 2006, 06:31 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

kronik · May 15th, 2006, 02:29 PM

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... :(

mmcdonal · May 16th, 2006, 06:17 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