Importing .sql files over to an Access table

[djmc]

I have an .sql file which is about 75MB in size. It consists of INSERT statements with the values I need to import into an Access table. Is there an easy way to import this on the fly like via a DoCmd statement or should I just parse the file line by line and execute these insert statements to import the data?

 

[SBendBuckeye]

Can you post a couple representive lines of what the data looks like so we can see what you have? Thanks!

Have a great day!

j2consulting@yahoo.com

 

[Zygor]

Funny.... I built this last Friday. Not exactly what you want. I create queries in the database based on the SQL. Shouldn't be too hard to insert them into a table instead. I change the file extension to txt as a first step.

Public FileStore, SQLInputFile
Public SQLstr As String
Public RS As New ADODB.Recordset
Public qdfNew As QueryDef
Public strSQL As String
Public strFilter As String
Public strInputFileName As String
Public rstSQL As ADODB.Recordset
Public OldName As String
Public NewName As String
Public strPartial As String

Private Sub cmdCreateAQuery_Click()

strOriginationFolder = "C:\Documents and Settings\Test\"
With Application.FileSearch
.NewSearch
.FileType = msoFileTypeAllFiles
.LookIn = strOriginationFolder
.SearchSubFolders = False
.Execute
intCountOfFiles = .FoundFiles.Count - 1
For Intj = 1 To .FoundFiles.Count
OldName = .FoundFiles(Intj)
NewName = Left(OldName, Len(OldName) - 4) & ".txt"
Name OldName As NewName
ExecSQL NewName
strInputFileName = Right(NewName, Len(NewName) - 53)
strPartial = Left(strInputFileName, Len(strInputFileName) - 4)
Set qdfNew = CurrentDb().CreateQueryDef(strPartial, SQLstr)
Next
End With

End Sub

Public Function ExecSQL(strFileToRead As String)
On Error Resume Next


Set FileStore = CreateObject("Scripting.FileSystemObject")
Set SQLInputFile = FileStore.OpenTextFile(strFileToRead)
SQLstr = ""

Do While Not SQLInputFile.AtEndOfStream
SQLstr = SQLstr & " " & SQLInputFile.readLine
RS.Open SQLstr, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, acDataQuery
Set RS = Nothing
Loop

SQLInputFile.Close
Set SQLInputFile = Nothing
Set FileStore = Nothing
End Function