Absolutely stuck Insert string to table with sql

[damcdon]

Hi All

In reference to the following thread :
http://www.access-programmers.co.uk/forums/archive/index.php/t-91354.html

I have managed to stumble through the "Recordset method" and am now able to insert a string into a table , the only problem I have is there is too much data so I assume it's not inserting each line as a row and trying to put it all into one field

Here is my code its a bit messy as im a newbie but any help you can provide is most appreciated

Function test13()

Dim db As DAO.Recordset
Dim ver1 As String

Set db = CurrentDb.OpenRecordset("QDN_Tidy", dbOpenTable)
Set Sleep = CreateObject("Toolsack.Sleeper")
Set soc = CreateObject("Socket.Tcp")

'Nortel DMS Telnet session
soc.Timeout = 4000
soc.DoTelnetEmulation = True
soc.TelnetEmulation = ""
soc.Host = "xx.xx.xx.xx:23"
soc.Open
soc.WaitFor ("login:")
soc.Wait
soc.SendLine ("damcdon") & vbCrLf
soc.WaitFor ("damcdon's Password:")
soc.SendText ("xxxx") & vbCrLf
soc.WaitFor ("$")
soc.SendText ("telnet cm") & vbCrLf
soc.WaitFor ("Enter User Name")
soc.SendText ("xxxx") & vbCrLf
soc.SendLine ("xxxx") & vbCrLf
soc.Wait
soc.SendLine ("qdn 1099124285") & vbCrLf
Sleep.Sleep 2
soc.WaitFor "qdn 1099124285"
'Sleep.Sleep 2
ver1 = soc.Buffer
soc.Close
'Debug.Print ver1

'This is the code in question
db.AddNew
db.Fields("Field1").Value = ver1
db.Update
db.Close


End Function

 

[RoyVidar]

What is the datatype of field1? An Access text field takes max 255 characters. For more, you'd need memo.

What is the result of what you're doing, how do you determine something is wrong? Is there an errormessage?

What is the result if you do a

Debug.Print ver1

Prior to adding it to the table (check it out in the immediate pane - Ctrl+G)

 

[damcdon]

Thank you for responding,

The data type in field1 is text and the result of ver1 is below but I think its trying to insert all of that into one field and what I need is one collum and multiple rows, similar to what a .txt import does.

Also the error message im getting reads Run-time error '3163 the field is too small to accept the amount of data....


Result of ver1:

"
qdn 1099124285

Enter Password

>*******

******* Logged in on 2007/07/25 at 08:44:55.

Previous login on 2007/07/24 at 19:53:34 from UNKNOWN VOLUME_ID #6D04

** Welcome to the A100 **

2006/05/12 22:21 <<*>> AUCKLAND ******* ISN07_XA DOB 13 05 2006 <<*>>

>

>qdn *******

-------------------------------------------------------------------------------

DN: *******

TYPE: SINGLE PARTY LINE

SNPA: 109 SIG: N/A LNATTIDX: N/A

LINE EQUIPMENT NUMBER: *******

LINE CLASS CODE: M5316 SET

KEY: 1

CUSTGRP: CLEAR_A100 SUBGRP: 0 NCOS: 1 RING: Y

CARDCODE: 6X21BC GND: N PADGRP: NZMBS BNV: NL MNO: Y

PM NODE NUMBER : 214

PM TERMINAL NUMBER : 4

DNGRPS OPTIONS:

NETNAME: PUBLIC

ADDRESS: DDNNNNNNNN

SUPPRESS_NAME

NETNAME: CLEARVPN

ADDRESS: DDNNNNNNNN

OPTIONS:

3WC RAG LNR NAME PUBLIC TELSTRACLEAR 05 CLEARVPN ******* ******* KSMOH

CFB N ******* A 1 CBU CFD N ******* A 1 CDU AUTODISP Y $ CFU N

1******* I 1 CFS N Y 10 MWT MWL N N SCS QTD



-------------------------------------------------------------------------------

>

>

"

 

[RoyVidar]

You want each row in the string into a new record? I'm not sure I understand the why, but that'll be my problem (untested code)

dim s()              as string
dim l                as long
s=split(ver1, vbCrLf)
for l = 0 to ubound(s)
    db.execute "INSERT INTO QDN_Tidy (field1) Values (""" & s(l) _
               """)",dbfailonerror
next

I'm assuming the it's both carriage return and line feed. Else, try experimenting with only vbCr (carriage return) or vbLf (only line feed).

Now, if you've done an accurate copy/paste, you may have more challenges at your hand. The above code should work if the table fields ZLS property is set to yes, I think (I never use that setting), or you'd need to rebuild a little to insert Null. I'm assuming you have at least one field in the table? For instance an Autonumber PK?

 

[damcdon]

Thank you very much

It works perfect with a few tweaks, the "why" is, im trying to return one bit of information from the query to run a command based on that bit of information.

Here is what I ended up using


Dim s() As String
Dim l As Long
s = Split(ver1, vbCrLf)
For l = 0 To UBound(s)

If s(l) Like "LINE EQUIPMENT NUMBER:*" & vbCr & "*" Then
strSQL = "INSERT INTO QDN_Tidy (field1) Values ('" & Left(Trim(s(l)), 43) & "');"
MsgBox l & " = " & s(l)
MsgBox strSQL
db.Execute strSQL
End If

Next