sql importing

[Guest]

I have a heap of files with a "sql" extenision, I know they belong to a sql
data base, I want to know how I go about putting them into a access database,
I think they are querys to create the dataabase they belong to, there is
about 50 of these, they all start like this,

CREATE TABLE dbo.invBlueprintTypes(
blueprintTypeID INTEGER,
parentBlueprintTypeID INTEGER,
productTypeID INTEGER,
productionTime INTEGER,
techLevel INTEGER,
researchProductivityTime INTEGER,
researchMaterialTime INTEGER,
researchCopyTime INTEGER,
researchTechTime INTEGER,
productivityModifier INTEGER,
materialModifier INTEGER,
wasteFactor INTEGER,
chanceOfReverseEngineering DOUBLE PRECISION,
maxProductionLimit INTEGER);

INSERT INTO dbo.invBlueprintTypes

(blueprintTypeID,parentBlueprintTypeID,productTypeID,productionTime,techLevel,researchProductivityTime,researchMaterialTime,researchCopyTime,researchTechTime,productivityModifier,materialModifier,wasteFactor,chanceOfReverseEngineering,maxProductionLimit)
VALUES(679,,48,120,1,2400,2400,2400,24000,24,5,10,0,1500);

INSERT INTO dbo.invBlueprintTypes

(blueprintTypeID,parentBlueprintTypeID,productTypeID,productionTime,techLevel,researchProductivityTime,researchMaterialTime,researchCopyTime,researchTechTime,productivityModifier,materialModifier,wasteFactor,chanceOfReverseEngineering,maxProductionLimit)
VALUES(681,986,165,600,1,12000,12000,12000,120000,120,5,10,0,300);

but not sure how to put that info into access,
thanks

 

[Nick Coe \(UK\)]

The first thing you could try is to copy and paste each part
of the sql into the Access query designer's sql view. You
may find some of the sql syntax needs amending but you won't
know for sure until you try. You may need to alter the
field types in the make statement.

The process would go something like this:

Open sql suffixed file in a text editor (Textpad for example
or PHPEditor) select and then copy the first part of the
text to the clipboard - the create table statement. SQL
staements are terminated by a semi-colon ;

Open your new db in Access, start a new blank query in the
query designer, go to the query designers' sql view and
paste the text you just copied.

Now edit that text to use the closest Access equivalent
field types IF necessary.

Try running the sql and edit the sql until it runs
correctly, deleting each new instance of the table until
it's right.

The sql you included in your post will, as you noted, try to
create a table called dbo.invBlueprintTypes.

Now repeat the process for the insert into statement which
will fill the table with data.

There are differences between flavours of SQL hence all the
re-editing aggro - this also mitigates against trying to
cook up some vba to directly read the sql text files and
then run the sql using something like DoCmd.RunSQL. If you
edited copies of the sql script files into Access compliant
SQL then you could try creating some vba to run the sql. It
really depends upon how often you intend to do this, in
other words, which is the least effort/cost and simplest way
to do it.

Good luck and hope that helps

--
Nick Coe (UK)
http://www.alphacos.co.uk/




In Dreamstar_1961 typed:

 

[Albert D. Kallal]

The problem is that all sql systems are some what differnt...

Access only supports one sql statement in a query at a time.

You could however write a loop to read one of those files...and execute the
sql.

I going to write this loop...and see what happens....

(I just pasted your example sql into a text file...and saved it)

Ok, first problem is that just looking at your examples, the first statement
will not work

ms-access does not supprot the database prefix "dbo.invBlueprintTypes".

Ok, so, lets just "remove" any text that has dbo.

I run again...
opps...again a problem..

Hum, it craps out on the insert statement...you have

INSERT INTO invBlueprintTypes ......
<snip>

VALUES(679,,48,120,1,2400,2400,2400,24000,24,5,10,0,1500)

The above value list is numbers..but it has a ",," in there. MS-access needs
a 0 between the blank set of commas in the above.
(it is possbile that a missing value in the above is to be considerd null --
I don't know).

Hum, that means we need to replace all ",," with a ",0,"

And, that blank could be at the start, or end of the values...so we have 3
cases

,, = ,0,
(, = (0,
,) = ,0)

Ok...that covers the blank value for the insert....lets run again..

Hey..it works!!

Here is the code. Remember, this is not production code..but code witten as
I typed this answer to you. In fact, I was just curioues how well (or not)
this could/wrould work...

it took less time to write the code then to write this email.

I don't know if you will have much luck here. If you can write code better
then writing english words, then perahps this will be of use to you...

...here is the code....


Sub isql()

Dim strSql As String
Dim strdata As String
Dim strF As String
Dim intF As String
Dim lngChars As Long
Dim vSql As Variant
Dim i As Integer

strF = InputBox("What sql file", , "C:\sql.txt")
If strF = "" Then Exit Sub

intF = FreeFile
Open strF For Input As intF
strdata = Input(LOF(intF), intF)
Close

strdata = Replace(strdata, vbCrLf, " ")
strdata = Replace(strdata, "dbo.", "")
strdata = Replace(strdata, "(,", "(0,")
strdata = Replace(strdata, ",,", ",0,")
strdata = Replace(strdata, ",)", "0,)")

vSql = Split(strdata, ";")

For i = 0 To UBound(vSql, 1)
strSql = Trim(vSql(i))
If strSql <> "" Then
CurrentProject.Connection.Execute strSql & ";"
End If
Next i


End Sub


You can cut and past the above into a module....it less then 20 lines of
code. I don't know what your other ddl stuff looks like.

Note the kludge of using the replace command to fix problems. Try the above
on a few more of those sql files. If it works, then you could extend the
code to read all of the sql files in one shot....