?
Solved

Filter on Transfer Database

Posted on 2005-03-28
14
Medium Priority
?
424 Views
Last Modified: 2008-03-10
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.

0
Comment
Question by:ispcorp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
14 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13643863
Hi ispcorp,

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
0
 
LVL 2

Author Comment

by:ispcorp
ID: 13643931
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?
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 13643987
ispcorp,

Strange.  So you currently have rights to a macro, but not to anywhere else in the source database.  Can you create a new .mdb, and then link tables, then create a query?

Hope this helps.
-Jim
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 2

Author Comment

by:ispcorp
ID: 13644091
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.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13644157
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.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13644318
Link the table instead of importing, and run an append query to transfer only the records you are interested in.
0
 
LVL 2

Author Comment

by:ispcorp
ID: 13644713
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?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13644792
Yes, they automatically update - the link is a "live" link, not a cache.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13646483
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?

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13646509
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.
0
 
LVL 2

Author Comment

by:ispcorp
ID: 13646681
Sorry about that...How do I ask for this to be reopened?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13646692
Just post a question in Community Support:
http://www.experts-exchange.com/Community_Support/

Include a link to this question, and they'll reopen it for you.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13646935
No sweat.  Once you get the hang of EE, you'll find it's a lot of fun.  TIA -Jim
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question