By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,285 Members | 1,687 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,285 IT Pros & Developers. It's quick & easy.

Export from .ADP to .MDB

P: n/a
i feel like this should be straight forward but for some reason it
doesn't appear to be. i have a subform in an adp file that contains a
large number of records (>65K) and the recordsource is created
dynamically at run time. i'm trying to figure out a way to export the
records to an mdb file without running the sql again.

i beleive the transferdatabase requires a saved query which translates
to a saved view on my sql box which isn't an option. one thought that
came to mind is creating the database in code, creating the
destination table and then looping thru the recordset (a
recordsetclone). there's obvious problems with that. for one, u'd
need to use adox or run ddl sql to create the table and two it seems
like it'd be slow as hell. in addition u'd need to bring up a common
dialog and yada yada yada. and if memory serves recordsetclones in
adp's are DAO so that's another reference i'd need.

any ideas on this?
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
CJ
Hi Ted,

My inclination would be to handle this on the SQL Server side. You could
create a stored procedure that would use your recordsource to create a table
and then use DTS to export to a data file. You could then schedule this
procedure in SQL Server or run this stored procedure from your adp using a
pass-through query. Hope this helps.

"Ted Theodoropoulos" <te********@yahoo.com> wrote in message
news:f5**************************@posting.google.c om...
i feel like this should be straight forward but for some reason it
doesn't appear to be. i have a subform in an adp file that contains a
large number of records (>65K) and the recordsource is created
dynamically at run time. i'm trying to figure out a way to export the
records to an mdb file without running the sql again.

i beleive the transferdatabase requires a saved query which translates
to a saved view on my sql box which isn't an option. one thought that
came to mind is creating the database in code, creating the
destination table and then looping thru the recordset (a
recordsetclone). there's obvious problems with that. for one, u'd
need to use adox or run ddl sql to create the table and two it seems
like it'd be slow as hell. in addition u'd need to bring up a common
dialog and yada yada yada. and if memory serves recordsetclones in
adp's are DAO so that's another reference i'd need.

any ideas on this?

Nov 12 '05 #2

P: n/a
thanks for the reply. i had that thought too. there's a couple
problems with that approach in this situation. one is that this app
was developed for a global group and the biggest group of users of the
app are in Asia. as a result, i don't have a existing network
directory that they have access to. yes i could create a share on the
sql server box and just give access to everyone (150K employees) but
i'm a little weary of that.

another issue that comes in to play is when multiple users send
requests. the databases will be way too big to email so i have to put
them in a network share and i'll have to create a routine to delete
old dbs periodically. also, unique name could be a problem or someone
taking someone elses database. plus the data is right there on the
client. it seems there should be some way to dump this to an MDB
without it being that big of a deal. maybe that's not a safe
assumption.

in 11+ years of access development i can't recall having such a
seemingly simple problem be such a PITA.

Hi Ted,

My inclination would be to handle this on the SQL Server side. You could
create a stored procedure that would use your recordsource to create a table
and then use DTS to export to a data file. You could then schedule this
procedure in SQL Server or run this stored procedure from your adp using a
pass-through query. Hope this helps.

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.