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

Export All MS Access Queries into External Text Files

P: n/a
Does anyone know of a utility or a method (VBA or otherwise)
to be able to export ALL 100+ MS Access Queries from an Access
Table into individual text files perhaps ending with .sql so they can
be parsed and converted by my program into SQL Server sProcs?
I need a Mass Exporter not a one at a time manual equivalent.

Please reply directly to: jf*********@gmail.com

Thanks.

Jun 4 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
jf*********@gmail.com wrote:
Does anyone know of a utility or a method (VBA or otherwise)
to be able to export ALL 100+ MS Access Queries from an Access
Table into individual text files perhaps ending with .sql so they can
be parsed and converted by my program into SQL Server sProcs?
I need a Mass Exporter not a one at a time manual equivalent.
Public Sub QueryList1()
Dim qdf As QueryDef
Dim strFileName As String
For Each qdf In CurrentDb.QueryDefs
If Left(qdf.name, 1) <> "~" Then
'you need to figure out TransferText command. Maybe
'you won't be lazy and expect people to read it to
'you and tutor you on how it works.
strFileName = qdf.Name & ".SQL"
Docmd.TransferText ....
End If
Next qdf
MsgBox "Done"
End Sub

Please reply directly to: jf*********@gmail.com
Why? You to lazy to retrieve responses? I guess you think we're all
your servants. Sorry King, you're the guy that needs and requested our
help, we don't need yours.

Thanks.

Jun 4 '06 #2

P: n/a

salad wrote:
jf*********@gmail.com wrote:
Does anyone know of a utility or a method (VBA or otherwise)
to be able to export ALL 100+ MS Access Queries from an Access
Table into individual text files perhaps ending with .sql so they can
be parsed and converted by my program into SQL Server sProcs?
I need a Mass Exporter not a one at a time manual equivalent.


Public Sub QueryList1()
Dim qdf As QueryDef
Dim strFileName As String
For Each qdf In CurrentDb.QueryDefs
If Left(qdf.name, 1) <> "~" Then
'you need to figure out TransferText command. Maybe
'you won't be lazy and expect people to read it to
'you and tutor you on how it works.
strFileName = qdf.Name & ".SQL"
Docmd.TransferText ....
End If
Next qdf
MsgBox "Done"
End Sub

Please reply directly to: jf*********@gmail.com


Why? You to lazy to retrieve responses? I guess you think we're all
your servants. Sorry King, you're the guy that needs and requested our
help, we don't need yours.

Thanks.
Oh thank you thank you, my liege.
I have posted for years on various SQL tips and techniques
I have just never had the privilege of laboring in the Access 97
and Access 2000 world thank god. And now we are putting to
rest over 50 Access apps by converting them to SQL Server
en masse and putting another Access dude out of business.
It should have been done in SQL Server in the first place.


Jun 5 '06 #3

P: n/a
Hi.
thank god.
That's " . . . thank God." Otherwise, you're addressing Salad as a god.
Perhaps the "Access god"? ;-)
I have posted for years on various SQL tips and techniques
Good thing you finally stopped by an Access newsgroup then. Your SQL
buddies failed to warn you that much of the spam in your Inbox has been due
to the fact that you've been posting your E-mail address in the newsgroups,
where spambots harvest E-mail addresses daily.
And now we are putting to
rest over 50 Access apps by converting them to SQL Server
en masse and putting another Access dude out of business.
And you can thank a bunch of Access dudes for providing you and your cohorts
with this (probably) paid job opportunity, as well as an opportunity to
learn a new skill or to improve one -- data migration from Jet. And
remember that those Access database applications were probably built by
people having various skill levels, so those schemas, constraints, indexes,
triggers, query optimizations, and record lock avoidance techniques need to
be looked at carefully, especially for data insertion and deletion anomolies
and ensured data integrity.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
<re*****@gmail.com> wrote in message
news:11*********************@i40g2000cwc.googlegro ups.com...
salad wrote:
re*****@gmail.com wrote:
> Does anyone know of a utility or a method (VBA or otherwise)
> to be able to export ALL 100+ MS Access Queries from an Access
> Table into individual text files perhaps ending with .sql so they can
> be parsed and converted by my program into SQL Server sProcs?
> I need a Mass Exporter not a one at a time manual equivalent.


Public Sub QueryList1()
Dim qdf As QueryDef
Dim strFileName As String
For Each qdf In CurrentDb.QueryDefs
If Left(qdf.name, 1) <> "~" Then
'you need to figure out TransferText command. Maybe
'you won't be lazy and expect people to read it to
'you and tutor you on how it works.
strFileName = qdf.Name & ".SQL"
Docmd.TransferText ....
End If
Next qdf
MsgBox "Done"
End Sub
>
> Please reply directly to: re*****@gmail.com


Why? You to lazy to retrieve responses? I guess you think we're all
your servants. Sorry King, you're the guy that needs and requested our
help, we don't need yours.
>
> Thanks.
Oh thank you thank you, my liege.
I have posted for years on various SQL tips and techniques
I have just never had the privilege of laboring in the Access 97
and Access 2000 world thank god. And now we are putting to
rest over 50 Access apps by converting them to SQL Server
en masse and putting another Access dude out of business.
It should have been done in SQL Server in the first place.
>

Jun 5 '06 #4

P: n/a
'69 Camaro wrote:
remember that those Access database applications were probably built by
people having various skill levels, so those schemas, constraints, indexes,
triggers, query optimizations, and record lock avoidance techniques need to
be looked at carefully, especially for data insertion and deletion anomolies
and ensured data integrity.


My first legacy rule: Start Over.

Jun 5 '06 #5

P: n/a
Hi, Lyle.
My first legacy rule: Start Over.
A very wise rule.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@h76g2000cwa.googlegr oups.com... '69 Camaro wrote:
remember that those Access database applications were probably built by
people having various skill levels, so those schemas, constraints,
indexes,
triggers, query optimizations, and record lock avoidance techniques need
to
be looked at carefully, especially for data insertion and deletion
anomolies
and ensured data integrity.


My first legacy rule: Start Over.

Jun 5 '06 #6

P: n/a
jf*********@gmail.com wrote in
news:11*********************@i40g2000cwc.googlegro ups.com:
And now we are putting to
rest over 50 Access apps by converting them to SQL Server
en masse and putting another Access dude out of business.
It should have been done in SQL Server in the first place.


Well, that's obviously a lie, since you can't replace the
application with SQL Server, only the data store.

Idiot.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jun 5 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.