DDL Query execution

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

0
Ale
11/9/2007 1:12:09 PM
access 16762 articles. 3 followers. Follow

3 Replies
765 Views

Similar Articles

[PageSpeed] 35

On Nov 9, 7:12 am, Ale <alkon...@freemail.it> wrote:
> 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

I am not sure, but the querys in Access allow you to change the view
of the query to straight SQL.
I know that you can open a new query (Queries|New) and off the menu
bar View|SQL view.
This would allow you to enter in your SQL.  I don't know if you would
be allowed to enter a series of
SQL Statements.

Hope this helps

MAC

0
MAC
11/9/2007 7:09:56 PM
 I will venture to say that it is not hardly practical.  Each statement has to
be enclosed in double quotes and executed separately. There are also differences
in functions and delimiters. Just an example:

Sub CreateProductPricesTable()
With CurrentProject.Connection

..Execute _
"CREATE TABLE ProductPrices" & _
"(sku INTEGER NOT NULL" & _
", price_date CHAR(8) NOT NULL" & _
", price DECIMAL (12,2) NOT NULL" & _
", PRIMARY KEY (sku,price_date,price));"

End With
End Sub


On Fri, 09 Nov 2007 05:12:09 -0800, Ale <alkondor@freemail.it> wrote:

>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
0
Michael
11/9/2007 7:35:20 PM
Thanks to all.
I think i'll modify my sql script file using my text editor macros
adding

..Execute _ "

and

"

before and after each statement, and so i'll copy all statements in a
Access module. It should work, right?
Good bye.

Ale

0
Ale
11/10/2007 1:16:24 PM
Reply:

Similar Artilces:

filter query based on single column list box
Thanks for taking the time to read my question. I want to filter this query by the values in my single column list box. I made this function to create the string, but the query doesn't like it. If I paste the string that the function returns into the criteria line the query works. What am I doing wrong? Thanks, Brad SQL: SELECT tblPeripheral.Type, tblPeripheral.Location, tblPeripheral.[Serial Number], tblPeripheral.Model, tblPeripheral.Brand, tblPeripheral.[Model 2], ExportPeripheralInfo() AS Expr1 FROM tblPeripheral WHERE (((tblPeripheral.Type)=ExportPeripheralInfo())); Func...

oledb query problem
Hello. I have a connection with a Access 2007 db back end (tables only) using OLEDB. The db is on a shared drive on the network. So a UNC is used. A simple query works but a query in which other tables are included just never works. Here is the query I'm trying to use; SELECT tblChangeRequest.ChangeRequestID, tblChangeRequest.ChangeRequest, tblChangeRequest.DevCompletionDate, tblProjectLead.ProjectLead, tblSPGName.SPGName, tblChangeRequest.QuickHit, tblChangeRequest.BugFix FROM (((tblChangeRequest LEFT JOIN tblProjectLead ON tblChangeRequest.Project...

Using Lookup Query with Calculated Value in Table ComboBox
Hello all, This question takes a complicated setup, but I'll try to keep it brief. I have a database with a main table (tblMain) and a lookup table (tblLookup). tblMain has an integer field (LookupMe) that is related to the ID field in tblLookup. I set up LookupMe to use a ComboBox input display control that uses a query as its source. The query contains two columns: tblLookup.ID and a calculated value, say "[First Name] & " " & [Last Name]". Back in tblMain, the LookupMe field has the following properties: Bound Column: 1 Column Count: 2...

querying a view
I've got 2 taables containing a couple of years worth of data and have created a view which joins them and does a couple of calculations. If I run a query against that view to select only the last weeks data, does SQL Server haave to joint the whole contents of each table before the where clause on the query is used? Thanks No. The query optimizer will attempt to optimize the query as a whole. That is, for example, if your view is Select <column list> From Table1 Inner Join Table2 On <condition>; and you do Select <column list> From <Your...

web query autoformats numbers
Hi I'm fetching data from a server (the URL of which I've excised for security reasons) using a web query as below. The problem is, sometimes the data is something like so: 8264E01 This is supposed to be interpreted as text. I can't seem to find a combination of settings that causes it to leave that as text. It always seems to convert that to a number and display it as 8.264E4. Is there any way to force this to leave it as text? I've tried: - locking the formatting but leaving the page editable (the query refuses to run) - formatting the destination as text - various s...

Queries calculation
I have a table called “openbills”. I have a query called “billsopenvalue” In the table I have a field where I insert the amount a customer paid – this can be as well a part payment of the bill. The query calculates how much is still pending to pay. Now the problem: Ie: the total to be paid amount is 1000. The customer paid 100. = Result of query 900 Now the customer paid another 100 – normally the result of the query should be 800; but it is again 900 because the resting value is calculated: total to be paid – paid amount = resting amount. How can I fix after each calculation the restin...

Date/Time Query
I am running a query that calculate the difference between two times and I would like the results in h:mm format If the difference is 2.5 days, the query returns 12 hours, but leaves the 2 days off. However, if the difference is .25 of a day, the query correctly returns 6 hours For 1.5 days I would like it to return 36:00 which is 36 hours...currently it returns 12 as anything over 24 is lost. Any help appreciated. See: Calculating elapsed time at: http://allenbrowne.com/casu-13.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http...

Placing query results in a cell
I have a project where I can connect to a database, construct a query within a VBA module: mysql01 = " select ChildID, ChildName " mysql02 = " from tblChild " Now suppose I want to put the results of the query anywhere in the spreadhseet, i.e. in a specific cell. How do I do that? On Nov 26, 4:55=A0pm, Tangier <nitinhemm...@gmail.com> wrote: > I have a project where I can connect to a database, construct a query > within a VBA module: > > mysql01 =3D " select ChildID, ChildName " > mysql02 =3D " from tblChild " > > Now suppo...

Update query
I have a table ("Table1") with a memo field ("p"). I need a query in SQL that replace any character "#" inside the memos with the chr(13). ...

ddl and transactions
i guess i never thought about this before but if i am changing a proc while a transaction is running the new proc is live the moment the alter is complete. I would have thought that procs are versioned like data is during a transaction. for example user1 starts tran user1 exec proc1 user2 alters proc1 user1 exec proc1 --this exec has user2's changes user1 commit i just tried it with a simple proc and it behaved that way. sql2005 Just something i noticed. How does DDL and transactions work? I thought one of the enhancements that 2005 had was DDL would now work in a tr...

query very slow
i have a query that takes about five minutes to run. i think that the delay is not justified: the table contains around 40000 records and the query is based on two separate queries whose performance is ok. below is the sql of my query if anyone could help me please! SELECT [Quotation-Suppliers-qry].[Order Number], [Quotation-Suppliers-qry].[PART number], [Quotation-Suppliers-qry].Manufacturer, [Quotation-Suppliers-qry].Item, [Quotation-Suppliers-qry].Description, [Net Price]-[MinOfNet Price] AS Expr4, [Pricelist-qry-0].[MinOfNet Price], [Quotation-Suppliers-qry].[Net Price], ...

Grouping dates in Microsoft Query
Hello, is it possible to aggregate lots of daily billing dates to 12 months in Microsoft Query, before returning the data to Excel? Thank you in advance. Kind regards, H.G. Lamy Excel version? Can you give an example of what the output would look like? --JP On Feb 2, 7:00=A0am, "H.G. Lamy" <Enterp...@web.de> wrote: > Hello, > > is it possible to aggregate lots of daily billing dates to 12 months in > Microsoft Query, before returning the data to Excel? > Thank you in advance. > > Kind regards, > > H.G. Lamy J...

between date and dateadd query
Hi all, I have just noticed that when i use the following query it is showing all results and disregarding the date. I am 99% sure this worked in the past and have not edited the query. The only thing that has changed is that we are now in the year 2008. I use the following, Between Date() And DateAdd("d",[Enter Number of Days to Expiration],Date()) Or >Now() This lets me enter a number and the report returns records thatare due within todays date plus the number of days i type in. I am sure this worked until the year actually changed to 2008, is this possible? any help app...

Database query won't open
I am trying to create a new database query from within Excel 2000. I was able to do this successfully yesterday from the same PC. Today, when I go to Data, Get External data, new database query, I get the hour glass and Excel then locks up. Anyone have any ideas? -- maryj ...

AutoFilter in Query not pulling blank cell
Hello, I have a query with this in the criteria: Like "*" & [Forms]![frmForm1]![cboNames] & "*" and in the cboNames I have : me.requery the autofilter cboNames works. But when I first open the form, cboNames is still blank, the form only query whose records containing something in the Names. please help, is there a way to pull everything, even blank records? I even tried: Like "*" & [Forms]![frmForm1]![cboNames] & "*" Or Is Null. but it messes everything up. I have several of these autofilter on my form. Switch to query to...

Query, Change SortVal
SortVal: IIf((Nz([qTotalDueForPayment].[TotalDue],0)-Nz([qPaidAmountForPayment].[PaidTotal],0))0,>1,1) This Code is sorting in this order $20 $0 -$20 How can I change it to this order $20 -$20 $0 So as zero balance will be last to show Thanks for any Help........Bob On Tue, 4 Dec 2007 13:35:05 +1300, "Bob V" <rjvance@ihug.co.nz> wrote: I might add another column, which displays True or False depending on if the SortVal is 0 or not: (IIf((Nz([qTotalDueForPayment].[TotalDue],0)-Nz([qPaidAmountForPayment].[PaidTotal],0))0,>1,1)=0) Then sort by that column first (I&...

LDAP Query Builder?
Are there interface tools to help build LDAP queries? Google is not forthcoming with them. -- jbfields@msn.com www.jbfields3.com "Dogs may have kept us company on the hunt, but it was the cats who insisted we invent houses and discover fire." -- Khiem Tran ...

Query question
Hi, I am stuck with a programming problem and I'm wondering if anyone can help me. Running SQL 2005. Here's a sample of my data: CREATE TABLE #temp (class varchar(1), referral varchar(2)) INSERT INTO #temp VALUES ('A', 'BB') INSERT INTO #temp VALUES ('A', 'AA') INSERT INTO #temp VALUES ('B', 'BB') INSERT INTO #temp VALUES ('B', 'CC') INSERT INTO #temp VALUES ('C', 'AA') INSERT INTO #temp VALUES ('C', 'BB') INSERT INTO #temp VALUES ('A', 'DD...

SelfJoin query
Hi: Can you please tell me how can I modify this query to identify these 'pairs': 1234A and 1234B; the below query is working when I have 1234A and 1234AX; the characters before X; or in the first case before A and B are the same. Thank you, Dan **** SELECT A1.DEALNO, B1.DEALNO FROM test AS A1 INNER JOIN test AS B1 ON B1.DEALNO = A1.DEALNO & "B"; Hi Marshall: Thank you! It gives me everything; I need just sameA and sameB.... Here is SQL: *** SELECT A.DEALNO, B.DEALNO FROM test AS A INNER JOIN test AS B ON Left(A.DEALNO, Len(A.DEALNO) - 1) = Left(B.DEALNO, Le...

Queries Links Change
I have a general question on Access 2003. I have several applications that run in the following manner: Step 1: Make Table queries runs and creates some large tables (extracting data out of ORACLE, etc.). Step 2: several other queries run for summing totals, etc. using tables from step 1 Step 3: Import process deletes tables from step 1 to clean up space in the database. The problem I seem to run into is that if someone goes into the queries from step 2 in design view when the tables are NOT existing in the database, the links and tables are not there and queries get saved with no link...

How to Creating a Query Report
Is there somewhere that I could get some information as to how the reports in RMS are composed. When you look at the various reports they all seem fairly simple in there basic structure. Are the any examples of how these reports are composed. The //--- Columns ---// iformation seems to be the same in most reports, same as the //--- Title Rows ---//. Setting up the flitters and the TablesQueried I need to understand. I have the schema for 1.2. How do I better understand the following or what do I need to learn. I wish I was in college again and had all the time in the world. //--...

DDL Scripts
Does Visio2k2 (Pro) allow generation of DDL from ER Diagram? TIA Yes it does, but you'll need to have Visual Studio .NET installed to get this functionality. It's actually part of Visual Studio .NET but the menu option is in Visio; weird, huh? "Vai2000" <nospam@microsoft.com> wrote in message news:uQoXuCBoEHA.3680@TK2MSFTNGP10.phx.gbl... > Does Visio2k2 (Pro) allow generation of DDL from ER Diagram? > > TIA > > ...

Microsoft Query: Parameter Query?
Hi, In Excel I have a connection to a database. This connection uses a Query. In this query I want to add parameters that will change. For instance, I want the query to return only rows of data between a 'start' and a 'finish' date. How can I accomplish that? Microsoft Help talks over "parameter query (parameter query: A type of query that, when you run it, prompts for values (criteria) to use to select the records for the result set so that the same query can be used to retrieve different result sets.)." I could not find where or how I can accomplish that. A...

Query related to Help button in Property sheets?
Hi In my application for showing a wizard, i m using Property Sheets(tab dialog boxes derived from CPropertyPage). I have 4 buttons on my wizard: Back, Next, Cancel & Help. My problem is that how do i implement the <Help> button. I have to open a .chm file as soon as the user clicks the <Help> button. But where do i write the code as there is no handler function in the class wizard for Help button. Handler functions for Back,Next & Finish is there? How do i implement the Help button? Waiting for suggestions Regards ...

show criteria data from a query on a report using that query
I have a report that runs a query with a date range parameter criteria. It works great, now I want to display the entered date range on the report. Is this posible or do I have to get the range on a form? In the query, I have the following criteria "Between [Enter From Date:] And [Enter To date:]" Now I want to display what the user entered on the report. In a control on the report enter ="Between " & [Enter From Date:] & " And " & [Enter To date:] You can refer to the parameters in the query as if they were fields (in most case...