error in code

[Arnie]

Hi all,
can anyone help the code below seems to be working however, i am being
asked for
the strAfter parameter Value. runing through the code in a watch window i
can see that strAfter = "Fred" but when it gets to the DoCmd.RunSQL StrSQL it
asks me for the parameter value . what have i done wrong?

strFile = "C:\Folder\File.fpm"
intFile = FreeFile()
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strBuffer
intEqualSign = InStr(strBuffer, "=")
If intEqualSign > 0 Then
strBefore = Left$(strBuffer, intEqualSign - 1)
strAfter = Mid$(strBuffer, intEqualSign + 1)
Select Case strBefore
Case "Name"
StrName = strAfter

StrSQL = "INSERT INTO [Table1] (Name)" & " SELECT strAfter"

DoCmd.RunSQL StrSQL


Thanks in advance

Arnie

 

[Douglas J. Steele]

strAfter needs to be outside of the quotes. However, since it's a text
value, you need to put quotes around it:

StrSQL = "INSERT INTO [Table1] ([Name]) VALUES (""" & strAfter & """)"

Note that that will fail if strAfter contains a double quote. Check my May,
2004 "Access Answers" column in Pinnacle Publication's "Smart Access" for
details. You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

Incidentally, Name isn't a good choice for a field name: it's a reserved
word, and using reserved words for your own purposes can lead to issues.

For a comprehensive list of names to avoid (as well as a link to a free
utility to check your application for compliance), see what Allen Browne has
at http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename your field, at least put square brackets
around it, as in my example.

 

[Arnie]

ruddy marvelous thanks so much Douglas.

I know this is a bit cheeky to ask. the first instance in the select case
(which i got from you yesterday (Thank You)) it adds a new record, thats fine
exactly what i want
but as it goes through and gets to the next piece of data this also adds a
new record whereas i need it to stay on the same record just insert into the
next column.
in other words for each seperate field it adds a new record.
Does that make sense?

strAfter needs to be outside of the quotes. However, since it's a text
value, you need to put quotes around it:

StrSQL = "INSERT INTO [Table1] ([Name]) VALUES (""" & strAfter & """)"

Note that that will fail if strAfter contains a double quote. Check my May,
2004 "Access Answers" column in Pinnacle Publication's "Smart Access" for
details. You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

Incidentally, Name isn't a good choice for a field name: it's a reserved
word, and using reserved words for your own purposes can lead to issues.

For a comprehensive list of names to avoid (as well as a link to a free
utility to check your application for compliance), see what Allen Browne has
at http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename your field, at least put square brackets
around it, as in my example.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi all,
can anyone help the code below seems to be working however, i am being
asked for
the strAfter parameter Value. runing through the code in a watch window i
can see that strAfter = "Fred" but when it gets to the DoCmd.RunSQL StrSQL
it
asks me for the parameter value . what have i done wrong?

strFile = "C:\Folder\File.fpm"
intFile = FreeFile()
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strBuffer
intEqualSign = InStr(strBuffer, "=")
If intEqualSign > 0 Then
strBefore = Left$(strBuffer, intEqualSign - 1)
strAfter = Mid$(strBuffer, intEqualSign + 1)
Select Case strBefore
Case "Name"
StrName = strAfter

StrSQL = "INSERT INTO [Table1] (Name)" & " SELECT strAfter"

DoCmd.RunSQL StrSQL


Thanks in advance

Arnie

 

[Arnie]

sorry just saw your other comments. i used name for an example as i would
prefer not to publish what i am working on. Thanks for the comments much
appreciated.

strAfter needs to be outside of the quotes. However, since it's a text
value, you need to put quotes around it:

StrSQL = "INSERT INTO [Table1] ([Name]) VALUES (""" & strAfter & """)"

Note that that will fail if strAfter contains a double quote. Check my May,
2004 "Access Answers" column in Pinnacle Publication's "Smart Access" for
details. You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

Incidentally, Name isn't a good choice for a field name: it's a reserved
word, and using reserved words for your own purposes can lead to issues.

For a comprehensive list of names to avoid (as well as a link to a free
utility to check your application for compliance), see what Allen Browne has
at http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename your field, at least put square brackets
around it, as in my example.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi all,
can anyone help the code below seems to be working however, i am being
asked for
the strAfter parameter Value. runing through the code in a watch window i
can see that strAfter = "Fred" but when it gets to the DoCmd.RunSQL StrSQL
it
asks me for the parameter value . what have i done wrong?

strFile = "C:\Folder\File.fpm"
intFile = FreeFile()
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strBuffer
intEqualSign = InStr(strBuffer, "=")
If intEqualSign > 0 Then
strBefore = Left$(strBuffer, intEqualSign - 1)
strAfter = Mid$(strBuffer, intEqualSign + 1)
Select Case strBefore
Case "Name"
StrName = strAfter

StrSQL = "INSERT INTO [Table1] (Name)" & " SELECT strAfter"

DoCmd.RunSQL StrSQL


Thanks in advance

Arnie

 

[Douglas J. Steele]

My assumption was that you'd read the entire file and then execute as many
SQL statements as were needed. (i.e. if your data was to go to 1 row in a
single table, you'd have 1 INSERT INTO statement, if it was to go into two
separate tables, you'd have 2 INSERT INTO statements and so on)

Perhaps the recordset alternative I mentioned would be better for you:

Dim rsNew As DAO.Recordset
Dim booCreated As Boolean
Dim intFile As Integer
Dim intEqualSign As Integer
Dim strFile As String
Dim strBuffer As String
Dim strBefore As String
Dim strAfter As String
Dim strSQL As String

strSQL = "SELECT IPAddress, DefaultGateway, PreferredDNS " & _
"FROM MyTable"

Set dbNew = CurrentDb.CreateRecordset(strSQL)

strFile = "C:\Folder\File.abc"
intFile = FreeFile()
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strBuffer
intEqualSign = InStr(strBuffer, "=")
If intEqualSign > 0 Then
strBefore = Left$(strBuffer, intEqualSign - 1)
strAfter = Mid$(strBuffer, intEqualSign + 1)
If booCreated = True Then
rsNew.Edit
Else
rsNew.AddNew
booCreated = True
End If
rsNew.Fields(strBefore) = strAfter
rsNew.Update
End If
Loop
Close #intFile
rsNew.Close
Set rsNew = Nothing

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

ruddy marvelous thanks so much Douglas.

I know this is a bit cheeky to ask. the first instance in the select case
(which i got from you yesterday (Thank You)) it adds a new record, thats
fine
exactly what i want
but as it goes through and gets to the next piece of data this also adds a
new record whereas i need it to stay on the same record just insert into
the
next column.
in other words for each seperate field it adds a new record.
Does that make sense?

strAfter needs to be outside of the quotes. However, since it's a text
value, you need to put quotes around it:

StrSQL = "INSERT INTO [Table1] ([Name]) VALUES (""" & strAfter & """)"

Note that that will fail if strAfter contains a double quote. Check my
May,
2004 "Access Answers" column in Pinnacle Publication's "Smart Access" for
details. You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

Incidentally, Name isn't a good choice for a field name: it's a reserved
word, and using reserved words for your own purposes can lead to issues.

For a comprehensive list of names to avoid (as well as a link to a free
utility to check your application for compliance), see what Allen Browne
has
at http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename your field, at least put square
brackets
around it, as in my example.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi all,
can anyone help the code below seems to be working however, i am being
asked for
the strAfter parameter Value. runing through the code in a watch
window i
can see that strAfter = "Fred" but when it gets to the DoCmd.RunSQL
StrSQL
it
asks me for the parameter value . what have i done wrong?

strFile = "C:\Folder\File.fpm"
intFile = FreeFile()
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strBuffer
intEqualSign = InStr(strBuffer, "=")
If intEqualSign > 0 Then
strBefore = Left$(strBuffer, intEqualSign - 1)
strAfter = Mid$(strBuffer, intEqualSign + 1)
Select Case strBefore
Case "Name"
StrName = strAfter

StrSQL = "INSERT INTO [Table1] (Name)" & " SELECT strAfter"

DoCmd.RunSQL StrSQL


Thanks in advance

Arnie

 

[Arnie]

Douglas thanks for that any reason why CurrentDb.CreateRecordset(strSQL)
causes an error "Method or Data member not found it seems its the
"Createrecordset thats causing a problem.

My assumption was that you'd read the entire file and then execute as many
SQL statements as were needed. (i.e. if your data was to go to 1 row in a
single table, you'd have 1 INSERT INTO statement, if it was to go into two
separate tables, you'd have 2 INSERT INTO statements and so on)

Perhaps the recordset alternative I mentioned would be better for you:

Dim rsNew As DAO.Recordset
Dim booCreated As Boolean
Dim intFile As Integer
Dim intEqualSign As Integer
Dim strFile As String
Dim strBuffer As String
Dim strBefore As String
Dim strAfter As String
Dim strSQL As String

strSQL = "SELECT IPAddress, DefaultGateway, PreferredDNS " & _
"FROM MyTable"

Set dbNew = CurrentDb.CreateRecordset(strSQL)

strFile = "C:\Folder\File.abc"
intFile = FreeFile()
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strBuffer
intEqualSign = InStr(strBuffer, "=")
If intEqualSign > 0 Then
strBefore = Left$(strBuffer, intEqualSign - 1)
strAfter = Mid$(strBuffer, intEqualSign + 1)
If booCreated = True Then
rsNew.Edit
Else
rsNew.AddNew
booCreated = True
End If
rsNew.Fields(strBefore) = strAfter
rsNew.Update
End If
Loop
Close #intFile
rsNew.Close
Set rsNew = Nothing

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

ruddy marvelous thanks so much Douglas.

I know this is a bit cheeky to ask. the first instance in the select case
(which i got from you yesterday (Thank You)) it adds a new record, thats
fine
exactly what i want
but as it goes through and gets to the next piece of data this also adds a
new record whereas i need it to stay on the same record just insert into
the
next column.
in other words for each seperate field it adds a new record.
Does that make sense?

strAfter needs to be outside of the quotes. However, since it's a text
value, you need to put quotes around it:

StrSQL = "INSERT INTO [Table1] ([Name]) VALUES (""" & strAfter & """)"

Note that that will fail if strAfter contains a double quote. Check my
May,
2004 "Access Answers" column in Pinnacle Publication's "Smart Access" for
details. You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

Incidentally, Name isn't a good choice for a field name: it's a reserved
word, and using reserved words for your own purposes can lead to issues.

For a comprehensive list of names to avoid (as well as a link to a free
utility to check your application for compliance), see what Allen Browne
has
at http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename your field, at least put square
brackets
around it, as in my example.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all,
can anyone help the code below seems to be working however, i am being
asked for
the strAfter parameter Value. runing through the code in a watch
window i
can see that strAfter = "Fred" but when it gets to the DoCmd.RunSQL
StrSQL
it
asks me for the parameter value . what have i done wrong?

strFile = "C:\Folder\File.fpm"
intFile = FreeFile()
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strBuffer
intEqualSign = InStr(strBuffer, "=")
If intEqualSign > 0 Then
strBefore = Left$(strBuffer, intEqualSign - 1)
strAfter = Mid$(strBuffer, intEqualSign + 1)
Select Case strBefore
Case "Name"
StrName = strAfter

StrSQL = "INSERT INTO [Table1] (Name)" & " SELECT strAfter"

DoCmd.RunSQL StrSQL


Thanks in advance

Arnie

 

[Douglas J. Steele]

Sorry, my typo. That should be CurrentDb.OpenRecordset(strSQL)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas thanks for that any reason why CurrentDb.CreateRecordset(strSQL)
causes an error "Method or Data member not found it seems its the
"Createrecordset thats causing a problem.

My assumption was that you'd read the entire file and then execute as
many
SQL statements as were needed. (i.e. if your data was to go to 1 row in a
single table, you'd have 1 INSERT INTO statement, if it was to go into
two
separate tables, you'd have 2 INSERT INTO statements and so on)

Perhaps the recordset alternative I mentioned would be better for you:

Dim rsNew As DAO.Recordset
Dim booCreated As Boolean
Dim intFile As Integer
Dim intEqualSign As Integer
Dim strFile As String
Dim strBuffer As String
Dim strBefore As String
Dim strAfter As String
Dim strSQL As String

strSQL = "SELECT IPAddress, DefaultGateway, PreferredDNS " & _
"FROM MyTable"

Set dbNew = CurrentDb.CreateRecordset(strSQL)

strFile = "C:\Folder\File.abc"
intFile = FreeFile()
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strBuffer
intEqualSign = InStr(strBuffer, "=")
If intEqualSign > 0 Then
strBefore = Left$(strBuffer, intEqualSign - 1)
strAfter = Mid$(strBuffer, intEqualSign + 1)
If booCreated = True Then
rsNew.Edit
Else
rsNew.AddNew
booCreated = True
End If
rsNew.Fields(strBefore) = strAfter
rsNew.Update
End If
Loop
Close #intFile
rsNew.Close
Set rsNew = Nothing

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

ruddy marvelous thanks so much Douglas.

I know this is a bit cheeky to ask. the first instance in the select
case
(which i got from you yesterday (Thank You)) it adds a new record,
thats
fine
exactly what i want
but as it goes through and gets to the next piece of data this also
adds a
new record whereas i need it to stay on the same record just insert
into
the
next column.
in other words for each seperate field it adds a new record.
Does that make sense?

:

strAfter needs to be outside of the quotes. However, since it's a text
value, you need to put quotes around it:

StrSQL = "INSERT INTO [Table1] ([Name]) VALUES (""" & strAfter &
""")"

Note that that will fail if strAfter contains a double quote. Check my
May,
2004 "Access Answers" column in Pinnacle Publication's "Smart Access"
for
details. You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

Incidentally, Name isn't a good choice for a field name: it's a
reserved
word, and using reserved words for your own purposes can lead to
issues.

For a comprehensive list of names to avoid (as well as a link to a
free
utility to check your application for compliance), see what Allen
Browne
has
at http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename your field, at least put square
brackets
around it, as in my example.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all,
can anyone help the code below seems to be working however, i am
being
asked for
the strAfter parameter Value. runing through the code in a watch
window i
can see that strAfter = "Fred" but when it gets to the DoCmd.RunSQL
StrSQL
it
asks me for the parameter value . what have i done wrong?

strFile = "C:\Folder\File.fpm"
intFile = FreeFile()
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strBuffer
intEqualSign = InStr(strBuffer, "=")
If intEqualSign > 0 Then
strBefore = Left$(strBuffer, intEqualSign - 1)
strAfter = Mid$(strBuffer, intEqualSign + 1)
Select Case strBefore
Case "Name"
StrName = strAfter

StrSQL = "INSERT INTO [Table1] (Name)" & " SELECT strAfter"

DoCmd.RunSQL StrSQL


Thanks in advance

Arnie