?
Solved

Filter on Transfer Database

Posted on 2005-03-28
14
Medium Priority
?
433 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
  • 5
  • 4
  • 4
13 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

621 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