Export from .ADP to .MDB

[Ted Theodoropoulos]

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?

[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?

[Ted Theodoropoulos]

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.