Large amount of Query on Access2000

Hi all,
how can I execute a large amount of DDL Queries on Access 2000?

I've a DDl/SQL Script file which defines a data structure with CREATE
TABLE, ALTER TABLE UPDATE TABLE, etc. etc.
I know how to execute this script on MySQL, MS Sql Server, but don't
know how execute it on access 2000 :(

Thank you all.
Bye

Ale

re: Large amount of Query on Access2000

On Nov 9, 7:12 am, Ale <alkon...@freemail.itwrote:Create a function in a forms' code module, or in a standard module.
It could be called from the menu, or from a command button (or half a
dozen less obvious ways). It would look something like this:
' -------------------------------------
' In a forms' code module...
' -------------------------------------
Private function PrepareData( )
dim db as dao.database
set db = currentdb( )
db.execute "CREATE TABLE tblSomeTable(FirstName TEXT, LastName
TEXT)"
...
set db=nothing
db.close
End Function

Now if you want a function that parses a script file and then runs the
code, then you will have to create it. It can be done, but it is not
for the novice programmer.

re: Large amount of Query on Access2000

On Nov 9, 7:12 am, Ale <alkon...@freemail.itwrote:Create a function in a forms' code module, or in a standard module.
It could be called from the menu, or from a command button (or half a
dozen less obvious ways). It would look something like this:
' -------------------------------------
' In a forms' code module...
' -------------------------------------
Private function PrepareData( )
dim db as dao.database
set db = currentdb( )
db.execute "CREATE TABLE tblSomeTable(FirstName TEXT, LastName
TEXT)"
...
set db=nothing
db.close
End Function

Now if you want a function that parses a script file and then runs the
code, then you will have to create it. It can be done, but it is not
for the novice programmer.

re: Large amount of Query on Access2000

On Nov 9, 8:12 am, Ale <alkon...@freemail.itwrote:What database engine are you using with Access 2000?
If your SQL script file was written for MS-SQL standards, and you are
simply using Access's default db engine (JET) then you may have to
modify the SQL.

If you're using MS-SQL as a db engine for Access and you don't have
any permissions issues you could use just paste the whole string into
a query sql window and run it.

If you have a whole bunch of procedures in a file that you want to run
you may have to write code that wll parse the string derived from the
file to smaller, more manageable parts, especially so if you're
changing changing permissions. Here's some I use, but it's for a
particular situation:

Private Const Delimiter As String = vbNewLine & "GO" & vbNewLine
Private Const GRANT_TO_CHANGE_FROM As String = "[FFDBA1\FFDBAESO]"
Private Const GRANT_TO_CHANGE_TO As String = "[FFDBAESO]"

Private Sub ExecuteSQLScript( _
ByVal Script As String)
Dim aSubScripts() As String
Dim SubScript As String
Dim z As Long
aSubScripts = Split(Script, Delimiter)
For z = 0 To UBound(aSubScripts)
aSubScripts(z) = Trim$(aSubScripts(z))
SubScript = aSubScripts(z)
If Len(SubScript) 255 Then
SubScript = left$(aSubScripts(z), InStr(255,
aSubScripts(z), vbNewLine))
End If
If Len(Replace(SubScript, vbNewLine, "")) 1 Then
If left$(SubScript, 3) <"SET" Then
If InStr(SubScript, GRANT_TO_CHANGE_FROM) <0 Then
If MsgBox("Change " _
& GRANT_TO_CHANGE_FROM _
& " to " _
& GRANT_TO_CHANGE_TO _
& "?", _
vbQuestion Or vbYesNo, _
"FFDBA") _
= vbYes Then
SubScript = _
Replace(SubScript,
GRANT_TO_CHANGE_FROM, GRANT_TO_CHANGE_TO)
End If
End If
If MsgBox("EXECUTE " & vbNewLine & SubScript,
vbQuestion Or vbYesNo, "FFDBA") = vbYes Then
CurrentProject.Connection.Execute aSubScripts(z)
End If
End If
End If
Next z
End Sub

Of course, maybe I've misread you and you want to do something far
simpler than this.