Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Creating A Normalized Table - need help with parsing 2

Status
Not open for further replies.
Elysynn

Elysynn

Technical User
Mar 18, 2004
82
US
Good Afternoon,

I've returned to a problem I was working on a few weeks ago. I attempted to use the suggestion Golom provided to me in the thread701-1232254. However, the number of characters exceeds 6000 in some of the fields. The query takes approximately 25 minutes to run - and I can't do anything with the data afterward - as soon as I touch the query window it starts to access the records and my CPU utilization hits 100% and never comes back down. I tried turning it into a Make Table query and it was still running 2 hours later...

So, I looked for a VBA solution and found it in thread707-923268. I've made some modifications to the code, but now I'm getting a type mismatch error.
Code: 
'modified code from RoyVidar [URL unfurl="true"]http://www.tek-tips.com/viewthread.cfm?qid=923268[/URL]
Sub ParseServerValue()
    Dim rs As ADODB.Recordset
    Dim strSql As String
    Dim arr() As String
    Dim lngCounter As Long
    Set rs = CurrentProject.Connection.Execute("tblServerBackendExt", , adCmdTable)
    Do While Not rs.EOF
        If InStr(rs.Fields("StationID"), "#;") > 0 Then
            arr = Split(rs.Fields("StationID").Value, "#;")
            For lngCounter = 0 To UBound(arr)
                strSql = "insert into tblServerBackendExtParsed (Node, [StationID]) " & _
                        "values ('" & rs.Fields("Node").Value & "','" & _
                        arr(lngCounter) & "',#" & _
                CurrentProject.Connection.Execute(strSql)
            Next lngCounter
        Else
            strSql = "insert into tblQfinitiBackendExtParsed (Node, [StationID]) " & _
                    "values ('" & rs.Fields("Node").Value & "','" & _
                    rs.Fields("StationID").Value & "',#" & _
            CurrentProject.Connection.Execute(strSql)
        End If
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
End Sub

The error is showing up in the first
Code: 
                CurrentProject.Connection.Execute(strSql)

The delimiter is #; I don't know if that is causing the problem or not... I appreciate any assistance you can offer.

TIA,
Elysynn
 
Sort by date Sort by votes
Hi, Elysynn,

I suspect your delimiter is causing the problem. The pound sign is the delimiter for date values in JetSQL.

HTH,

Ken S.
 
Upvote 0 Downvote
I've removed the # from the #; delimiter. The code now reads as follows:
Code: 
'modified code from RoyVidar [URL unfurl="true"]http://www.tek-tips.com/viewthread.cfm?qid=923268[/URL]
Sub ParseServerValue()
    Dim rs As ADODB.Recordset
    Dim strSql As String
    Dim arr() As String
    Dim lngCounter As Long
    Set rs = CurrentProject.Connection.Execute("tblServerBackendExt", , adCmdTable)
    Do While Not rs.EOF
        If InStr(rs.Fields("StationID"), ";") > 0 Then
            arr = Split(rs.Fields("StationID").Value, ";")
            For lngCounter = 0 To UBound(arr)
                strSql = "insert into tblServerBackendExtParsed (Node, [StationID]) " & _
                        "values ('" & rs.Fields("Node").Value & "','" & _
                        arr(lngCounter) & "',#" & _
                CurrentProject.Connection.Execute(strSql)
            Next lngCounter
        Else
            strSql = "insert into tblQfinitiBackendExtParsed (Node, [StationID]) " & _
                    "values ('" & rs.Fields("Node").Value & "','" & _
                    rs.Fields("StationID").Value & "',#" & _
            CurrentProject.Connection.Execute(strSql)
        End If
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
End Sub

I am still getting the Error 13 in the same place...

-Elysynn
 
Upvote 0 Downvote
You still have pound signs in your SQL statement. Since it's a reserved character, if you want to use it as a literal, I think you have to double it up, i.e. "##".

HTH,

Ken S.
 
Upvote 0 Downvote

Eupher - Thanks for the response.
Still getting the error...
All # signs have been removed from the code.

This is the most recent version:
Code: 
    Dim rs As ADODB.Recordset
    Dim strSql As String
    Dim arr() As String
    Dim lngCounter As Long
    Set rs = CurrentProject.Connection.Execute("tblQfinitiBackendExt", , adCmdTable)
    Do While Not rs.EOF
        If InStr(rs.Fields("StationID"), ";") > 0 Then
            arr = Split(rs.Fields("StationID").Value, ";")
            For lngCounter = 0 To UBound(arr)
                strSql = "INSERT INTO tblQfinitiBackendExtParsed (Node, StationID)" & _
                         " VALUES ('" & rs.Fields("Node").Value & "','" & _
                           arr(lngCounter) & "')" & _
                CurrentProject.Connection.Execute(strSql)

            Next lngCounter
        Else
            strSql = "insert into tblQfinitiBackendExtParsed (Node, StationID) " & _
                    "values ('" & rs.Fields("Node").Value & "','" & _
                    rs.Fields("StationID").Value & "')" & _
            CurrentProject.Connection.Execute(strSql)
        End If
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    
End Sub

All fields being referenced in both tables are text fields.

-Elysynn
 
Upvote 0 Downvote
Hmmm...

Two things present themselves to me, but I don't know if they would cause the code break you are experiencing:
Code: 
strSql = "INSERT INTO tblQfinitiBackendExtParsed (Node, StationID)" & _
" VALUES ('" & rs.Fields("Node").Value & [COLOR=red]"','"[/color] & _
arr(lngCounter) & "')" [COLOR=red]& _[/color]
A missing space after the comma, and an extra concatenation operator and line continuation character. Also, just a matter of preference, no need to refer to the Fields collection when you're using the literal field name. I would write it like so:
Code: 
strSql = "INSERT INTO tblQfinitiBackendExtParsed (Node, StationID)" & _
" VALUES ('" & rs![Node] & "', '" & _
arr(lngCounter) & "')"
But haven't tested, so can't say if this will fix anything...

HTH,

Ken S.
 
Upvote 0 Downvote
As Eupher pointed out, you are concatenating your
SQL statement WITH YOUR EXECUTE STATEMENT???
...in both cases???

...rs.Fields("StationID").Value & "')"
CurrentProject.Connection.Execute(strSql),,129

",,129" doesn't return a recordset,...less overhead.
Always use it after an execute method, when you don't want to return a recordset, just run an action query.


Check your array elements, they may have quotes in them already. Hover your cursor over the values statement,
where the array is. See if it shows the value with quotes
already, you MAY have to remove it from the SQL.

..." VALUES ('" & rs.Fields("Node").Value & "'," & _
arr(lngCounter) & ")
 
Upvote 0 Downvote
Thank you both for you help. Below is the code that works and takes about 2 seconds to run...
Code: 
    Dim rs As ADODB.Recordset
    Dim strSql As String
    Dim arr() As String
    Dim lngCounter As Long
    Set rs = CurrentProject.Connection.Execute("tblQfinitiBackendExt", , adCmdTable)
    Do While Not rs.EOF
        If InStr(rs.Fields("StationID"), ";") > 0 Then
            arr = Split(rs.Fields("StationID").Value, ";")
            For lngCounter = 0 To UBound(arr)
                strSql = "INSERT INTO tblQfinitiBackendExtParsed (Node, StationID)" & _
                         " VALUES ('" & rs.Fields("Node").Value & "','" & _
                           arr(lngCounter) & "')"
                CurrentProject.Connection.Execute (strSql), , 129
                

            Next lngCounter
        Else
            strSql = "insert into tblQfinitiBackendExtParsed (Node, StationID) " & _
                    "values ('" & rs.Fields("Node").Value & "','" & _
                    rs.Fields("StationID").Value & "'" & ")"
            CurrentProject.Connection.Execute (strSql), , 129
            
        End If
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    
End Sub

I removed the [blue]& _[/blue] from the end of the SQL before the Execute statement, and added the ,,129.

I don't know if this will be useful to anyone else, but it was certainly helpful to me. Syntax seems to be my biggest hang-up... A star to each of you :)

-Elysynn
 
Upvote 0 Downvote
Why not simply this ?
Code: 
...
    Do While Not rs.EOF
        arr = Split(rs.Fields("StationID").Value, ";")
        For lngCounter = 0 To UBound(arr)
            strSql = "INSERT INTO tblQfinitiBackendExtParsed (Node, StationID)" & _
                     " VALUES ('" & rs.Fields("Node").Value & "','" & _
                     arr(lngCounter) & "')"
            CurrentProject.Connection.Execute strSql, , 129
        Next lngCounter
        rs.MoveNext
    Loop
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

M
  • Locked
  • Question
display a calendar with appointments in body of html email with access vba
Replies
1
Views
3K
SoftwareRT
SoftwareRT
Dan Stoica
  • Locked
  • Question
Problems with Foxpro dsnless linked tables
Replies
0
Views
372
Dan Stoica
Dan Stoica
DarkOne72
  • Locked
  • Question
Message Box Popup on certain days help !
2
Replies
23
Views
4K
Norman Sherlock
Norman Sherlock
Zyborg
  • Locked
  • Question
Using a variable as reference to a form
Replies
5
Views
469
Zyborg
Zyborg
Moss100
  • Locked
  • Question
Transfer data from external table to identical local table - urgent please
2
Replies
27
Views
784
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top