Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
krausr79
Starting Member
19 Posts |
 Posted - 2015-02-03 : 14:41:07
|
| We're using MS Access 2010 as a frontend to an SQL server back-end. From Access, I can run read-only queries and pass-through queries. I'd like to use a local Access table as part of a join to server data. As a non-pass-through query, it's slow; about 5 min to join to 2 other tables.I could use VBA to turn the local table into part of a pass-through query, with a large in() statement, or several where x='' ors but the local table may have 50000 entries in it. Is there a good or right way to pass this data in the query if I don't have write access to the server? |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2015-02-04 : 06:14:16
|
| You will need to talk to the SQL Server guy to allow permissions, create a SQL logon and pass it in the VB.We are the creators of our own reality! |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2015-02-04 : 06:30:47
|
| There's really no good way to join local and remote tables together. The 2nd option you mention is unwieldy but would probably give better performance than the first.Since SQL Server 2008 there's a table type feature, which supports sending a table definition and rows of data as a variable or parameter to SQL Server. A general outline here:https://technet.microsoft.com/en-us/library/bb522526%28v=sql.105%29.aspxI haven't used them myself, and not sure how VBA would support them, but in theory you could create the table type, populate it with Access data, and then pass it to a stored procedure that uses it. You'd have to write such a procedure and pass it to someone who has permission to create it in SQL Server. |
|
|
|
|
|
|
join to a list of values during pass-through