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
