ispcorp
asked on
Filter on Transfer Database
Hello,
I have a database that I use a macro, which calls "TransferDatabase", to bring over one of the tables from the source database. Is there a way to filter the data coming over from the source DB?
Specifically I wish to filter out any records that have the letter "N" in the 11th index of the string.
Your help would be greatly appreciated....Thanks.
I have a database that I use a macro, which calls "TransferDatabase", to bring over one of the tables from the source database. Is there a way to filter the data coming over from the source DB?
Specifically I wish to filter out any records that have the letter "N" in the 11th index of the string.
Your help would be greatly appreciated....Thanks.
ASKER
Trouble with that idea is that I'm not allowed to touch the source database. They are very sensitive about that around here? Any other way, you can think of?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, i could probably do that, but then I would have to put the new access db on everybodies machine. I'm not sure but I think this is more of an optimization request, and for some reason the fields that has values of "N" in the 11th index of the string is coming up alot. I don't want to add more overhead than necessary. I'm surprised that there is not anything we can do via the "Transfer Database" mehtod. Are you sure that is the best way? I was thinking that there could be something we could do via VB script, to mimic the "Transfer Database", and do a filter at the same time.
I'm more of a SQL Server/Oracle db guy and getting back to the basics is rough because I forgot all this stuff.
I'm more of a SQL Server/Oracle db guy and getting back to the basics is rough because I forgot all this stuff.
DoCmd.TransferDatabase doesn't have a filter or where parameter you can pass, so it looks like your options are limited to having somebody change the macro for you, create a new macro with your specifications, or my approach of creating a query and incorporating that. Since I'm not at your worksite, you'll have to determine which of these options is more feasable.
Link the table instead of importing, and run an append query to transfer only the records you are interested in.
ASKER
Do linked tables automatically update itself if the source table is updated, or do I have to put code to refresh the link table everytime the Macro is ran? If so, how would I go about refreshing the linked table in a macro?
Yes, they automatically update - the link is a "live" link, not a cache.
ispcorp - I believe I made an earlier comment which is extremely similar to the one you ultimately accepted. Please be more careful next time when accepting answers.
>Can you create a new .mdb, and then link tables, then create a query?
>Can you create a new .mdb, and then link tables, then create a query?
I think Jim is correct (sorry Jim, missed your statement about linking). You should ask for this question to be reopened and award the points to jimhorn instead.
ASKER
Sorry about that...How do I ask for this to be reopened?
Just post a question in Community Support:
https://www.experts-exchange.com/Community_Support/
Include a link to this question, and they'll reopen it for you.
https://www.experts-exchange.com/Community_Support/
Include a link to this question, and they'll reopen it for you.
No sweat. Once you get the hang of EE, you'll find it's a lot of fun. TIA -Jim
Sure. Instead of referencing the table in your DoCmd.TransferDatabase, create a query that has all of the 'filters' you need, test it to make sure it's exactly what you want to transfer, then reference the query instead of your table.
Hope this helps.
-Jim