| Author |
Topic |
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
 Posted - 2005-05-19 : 10:50:21
|
| Hi guys. In a program we are about to make we will allow people to download a script file containing all queries needed to recreate a small database on the server. For example the following is a script that the user would download to create a sample employee database. CREATE TABLE Employee (EmployeeID int PRIMARY KEY, Name varchar(10));INSERT INTO Master(EmployeeID, Name)SELECT 1, 'Dustin' UNION ALLSELECT 2, 'Brandon' UNION ALLSELECT 3, 'Sam'CREATE TABLE HoursWorked(EmployeeID int CONSTRAINT FK_Employee_HoursWorked FOREIGN KEY(EmployeeID) REFERENCES Employee(EmployeeID), HoursWorked int)INSERT INTO HoursWorked(EmployeeID, HoursWorked)SELECT 1, 8 UNION ALLSELECT 1, 10 UNION ALLSELECT 1, 4 UNION ALLSELECT 2, 2 UNION ALLSELECT 2, 5 UNION ALLSELECT 2, 8 UNION ALLSELECT 3, 8 With this file it is no problem to move the data into an instance of SQL Server because I can just execute it in query anaylzer. However, I'm not having any luck executing this script in Access. I tried going to SQL View and pasting the script file in there however, it appears that the SQL View mode only executes a single query at a time and will throw an error if you paste multiple queries inside of it. Is there an equilvalent to Query Analyzer in Access where I can execute the entire script with the push of a single button?Any help would be appreciated.Dustin Michaels |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-05-19 : 11:03:23
|
| Do you need need a database creation script in Access? Would just a clean MDB file as a template work? When you need to "create" a database, you just copy the MDB file.- Jeff |
 |
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2005-05-19 : 11:13:31
|
quote:
Originally posted by jsmith8858
Do you need need a database creation script in Access? Would just a clean MDB file as a template work? When you need to "create" a database, you just copy the MDB file.- Jeff
I'm not 100% what I really need. The only thing that the users will get is the script file containing the DDL and DML statements needed to recreate the database. If a user has this file it is very easy to recreate the database in Microsoft SQL Server because they just copy the contents of the script file in Query Analyzer and run it. However, I haven't found a way to execute a script file in Access so thats why I posted here  Dustin Michaels |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-05-19 : 11:18:35
|
| Are you trying to script out the creation of objects + data in a SQL Server database or in an Access database? What is being altered by this script, an MDB file or a SQL Server database?- Jeff |
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2005-05-19 : 12:57:54
|
quote:
Originally posted by jsmith8858
Are you trying to script out the creation of objects + data in a SQL Server database or in an Access database? What is being altered by this script, an MDB file or a SQL Server database?- Jeff
I'm trying to script out the creation of objects + data in a SQL Server database.The way the program works is that users can define a dynamic form page in our ASP.NET content management system in which they can specify the text boxes, drop down lists, etc. that will go on the form. This creates a dynamic number of tables in our SQL Server database. One of the links on the dynamic form page will allow the user to download the script file containing the DDL and DML statements to get an exact duplicate of the dynamic tables (including their data). The ASP.NET program is generating the SQL script and I want to be able to execute this script in Access to get a duplicate of the database.Currently I can get an exact duplicate in another Microsoft SQL Server database because I can execute the script file produced by the ASP.NET page in query analyzer. However, I haven't found a program like query analyzer for Access to execute the script.Dustin Michaels |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-05-19 : 13:50:36
|
| >>I'm trying to script out the creation of objects + data in a SQL Server database.>>However, I haven't found a program like query analyzer for Access to execute the script.Again, you are not being clear. Forget about what creates the script. Assume it's there. What is the purpose of the script? It is creating a Microsoft Access .MDB file, or is it creating a Microsoft SQL Server database on a server? I understand you want to create a duplicate database for the users, but is that duplicate a MS Access MDB file or a SQL Server database. Does this make sense?- Jeff |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-05-19 : 13:59:17
|
| Or, to come at Jeff's question from a different point of view, WHY are you trying to run the script in Access? How did Access come into the process? As you have seen, Access and SQL Server are two very different animals.---------------------------EmeraldCityDomains.com |
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2005-05-19 : 16:25:23
|
quote:
Originally posted by jsmith8858
>>I'm trying to script out the creation of objects + data in a SQL Server database.>>However, I haven't found a program like query analyzer for Access to execute the script.Again, you are not being clear. Forget about what creates the script. Assume it's there. What is the purpose of the script? It is creating a Microsoft Access .MDB file, or is it creating a Microsoft SQL Server database on a server? I understand you want to create a duplicate database for the users, but is that duplicate a MS Access MDB file or a SQL Server database. Does this make sense?- Jeff
Crap I'm sorry I must of read your previous post to fast. I want to create an Access .MDB file from the script downloaded from the ASP.NET page so people can run their own reports on the data.Dustin Michaels |
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2005-05-19 : 16:34:44
|
quote:
Originally posted by AjarnMark
Or, to come at Jeff's question from a different point of view, WHY are you trying to run the script in Access? How did Access come into the process? As you have seen, Access and SQL Server are two very different animals.---------------------------EmeraldCityDomains.com
The reason why I'm trying to run the script in Access is that the people who create the dynamic forms in our content management system want to download the information stored in the dynamic tables created by the dynamic forms so they can run their own reports in Access. Currently our content management system does not allow reports to be run on our dynamic form tables so this is a workaround for now.Dustin Michaels |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-05-19 : 17:22:34
|
| Ah, okay, that makes some sense. Unfortunately, the SQL syntax is just different enough that a SQL Server script won't run unhindered in Access, even if you found a method to run multiple commands in one script.However, if reporting is really all you're after, what about allowing them to use Access to connect to the SQL Server via linked tables and build their reports that way? Or better yet, Install SQL Reporting Services and build reports in that.If you really, really want each of them to have their own copy in their own Access, then I'm thinking you'll have to create a fancy DTS task to export and create the MDB for them from inside SQL Server.---------------------------EmeraldCityDomains.com |
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2005-05-24 : 11:16:25
|
| I'm still need help on this issue. If you guys have any solutions please respond.Dustin Michaels |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-05-24 : 11:33:48
|
| to execute multiple DDL language statements in MS Access, you'd need to loop through them 1 at a time, since as you mentioned it appears that JET only supports 1 SQL statement per "query".so, the first thing you'd need to do is decide how and where to store these statements. Do you want to store a bunch of text files? 1 large text file with "delimiters" between statements? Do you want to put each statement in a row in a table and enumerate that? Let me know how you decide to physically store and logically separate these SQL statements and then I can help you decide how to execute them. But the basic idea is:for each <SQL Statement> in <List of statements> currentdb.execute <SQL Statement>nextThat's essentially what query analyzer does -- the "GO" command isn't T-SQL, remember, it's a delimiter in a .sql text file that seperates one batch of SQL statements from the next. Do not forget what Mark has mentioned -- JET SQL and T-SQL are NOT compatible, especially the DDL syntax. Some basic commands will work, but the datatypes have different names and anything slightly complex like constraints probably not work.Also as Mark mentioned, I think you might be better off using DTS or something like that to essentially export your SQL database into an Access MDB file. Then you just deliver the MDB file to the client -- no scripting needed, and the data is all there as well.- Jeff |
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2005-05-24 : 11:42:42
|
I'll try what you guys posted. Its to bad there isn't an easier way to do this  Dang AccessDustin Michaels |
|
|
|
bootn
Starting Member
5 Posts |
Posted - 2005-05-24 : 21:21:38
|
| Ok i'm not really fluent in this type of thing but is it possible to create the script as a macro? I'm not a big fan of Macro's myself but if they were to import a macro and run it it can contain multiple sql statements on each row and execute them 1 by 1. But i dont know how you would go about creating a dynamic macro....Just a thought though |
|
|
|
|
Running a script in Access