Insertdata into ACCESS table from Recordset 4

[jpl458]

Want to insert a record from a recordset into and existing empty ACCESS table. The following code is inside some loops, but the code bombs on the docmd.RunSQL addata with this error:
Invalid use of '.','!',or (). in query expression
BallsRs.Fields(0).Value.
Just testing to get the insert running so only inserting one field for now-the first column in the record set.

Dim addata As Variant
addata = "INSERT INTO BallsTableTest(Account_Number) Select BallsRs.fields(0).value as Account_Number"
DoCmd.SetWarnings False
docmd.RunSQL addata
DoCmd.SetWarnings True

Thanks in advance

jpl

 

[PHV]

addata = "INSERT INTO BallsTableTest(Account_Number) VALUES (" & BallsRs.Fields(0).Value & ")"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

Do yourself a favor and do this in the future:

Dim addata As [s]Variant[/s] String
addata = "INSERT INTO BallsTableTest(Account_Number) Select BallsRs.fields(0).value as Account_Number"
[blue]
Debug.Print addata[/blue]

DoCmd.SetWarnings False
docmd.RunSQL addata
DoCmd.SetWarnings True

This way you can SEE what's going on.

Have fun.

---- Andy

 

[jpl458]

Never been into this part of VBA before. I don't understand the need for the &s in PHVs solution. I thought Variant data type would handle everything. In Andy's solution, what if some of the other fields I want to add are not strings, but numeric fields?(Some are in the destination table) Should I keep the Variant data type and use the operators to distinguish between types being inserted?

Thanks for the quick responses and the help

jpl

 

[Andrzejek]

In your original statement:
[tt]
addata = "INSERT INTO BallsTableTest(Account_Number) Select [blue]BallsRs.fields(0).value[/blue] as Account_Number"
[/tt]

The BLUE part was the value of a Field, that's way I suggested Debug.Print because that's what your data base gets. So that's why you need:
[tt]
addata = "INSERT INTO BallsTableTest(Account_Number) VALUES ("[blue] & BallsRs.Fields(0).Value & "[/blue])" [/tt]

Variant data type will handle everything, you are right, but why use a dump truck to move a handfull of dirt?

Other fields (some examples):
[tt]
addata = "INSERT INTO BallsTableTest(Account_Number, TextField, DateField) " _
& " VALUES (" & BallsRs.Fields(0).Value & ", '" & txtBox.Text & "', #" & MyDate.Text & "#)" [/tt]

Single quotes around Text field, #'s around dates.

Have fun.

---- Andy

 

[jpl458]

Thanks Andy. I'm going to have to noodle with this till I really understand all the punctuation. I just keep adding a bit at a time. BTW there are over thirty columns in the table I am building, stuff like Fiscal year, calendar year, week day, day name.... all as input into an Excel Pivot table.

Thanks

jpl

 

[Andrzejek]

It is not that difficult if you approach it this way:

Create your INSERT statement with all values hard-coded:[tt]
addata = "INSERT INTO MyTable(NumberField, TextField, DateField) " & _
" VALUES([red]123[/red], '[green]Some Text[/green]', #[blue]12/31/2013[/blue]#)"
[/tt]
So you have all numbers just numbers, all text surrounded by single quote, and all date with # around them. Then, since addata is just a string, replace all hard-coded (colored) values with the pieces from your recordset, leaving all commas, single quotes and # untouched.

Word of warning:
If you can have any text value with the ' inside them, replace one ' with 2 of them:[tt]
Replace(SomeText, "'", "''")[/tt]

Have fun.

---- Andy

 

[jpl458]

If it's a string I am assuming you don't need the concatenation characters as per your earlier variant example. If that is true it's a lot simpler.
I like the hard coded tip. I have been using a variant to hold the SQL, and I can't get a handle on what the ' " and &s are doing. I simply use the pattern and replicate it one field at a time, execute the code to see if it works. I build the SQL in Word (so I have copy) the paste it into the code.

Thanks a load for your help. If I could email you a Beer, I would.

jpl

 

[Andrzejek]

It would be beneficial to you to understand all the [tt]'" & xyz & "'[/tt] syntax.

Your SQL is just a string that you send to your DB. You start and end the string with "
To append to a string you use &, and continuation character is _

Myself I usually name my string strSQL because it's short

So if you have:[tt]
strSQL = INSERT INTO MyTable(SomeTextField) " & _
" VALUES('[blue]Bob Brown[/blue]')"[/tt]

And you have a txtBox with [blue]Bob Brown[/blue] in it, you would do this:
[tt]
strSQL = INSERT INTO MyTable(SomeTextField) " & _
" VALUES('[red]" &[/red] [blue]txtBox.Text[/blue][red] & "[/red]')"[/tt]

Notice that I replaced just the BLUE part of the SQL with the value of a text box, I did not change any BLACK original syntax.

After [tt]" VALUES('[/tt] I had to use " to end my string, & to add to it, then use txtBox's Text, then & to add to the string, then " to start the end of the added string.

Clear as mud, right?

PS. I take Heineken

Have fun.

---- Andy

 

[MajP]

If doing this a lot, or working with long and complicated inserts, these helper functions can save a lot of time and frustration. Especially when dealing with dates, and mixed values, and possible text with apostrophe

Demo

Public Sub Test()
  Dim flds As String
  Dim Vals As String
  Dim strSqlInsert As String
  
  flds = insertFields("FirstName", "LastName", "OrderID", "OrderDate")
  Vals = insertValues(sqlTxt("Mike"), sqlTxt("O'Brien"), Null, SQLDate(Date))
  strSqlInsert = createInsert("MyTableName", flds, Vals)
  Debug.Print strSqlInsert
End Sub

This produces the following correctly formatted sql string

INSERT INTO MyTableName ([FirstName], [LastName], [OrderID], [OrderDate]) VALUES ('Mike', 'O''Brien', NULL, #11/22/2013#)

The helper functions

Public Function insertFields(ParamArray varfields() As Variant) As String
  Dim fld As Variant
  For Each fld In varfields
    If insertFields = "" Then
      insertFields = "([" & fld & "]"
    Else
      insertFields = insertFields & ", [" & fld & "]"
    End If
  Next fld
  If Not insertFields = "" Then
    insertFields = insertFields & ")"
  End If
End Function
Public Function insertValues(ParamArray varValues() As Variant) As String
  Dim varValue As Variant
  For Each varValue In varValues
    If IsNull(varValue) Then varValue = "NULL"
    If insertValues = "" Then
      insertValues = "(" & varValue
    Else
      insertValues = insertValues & ", " & varValue
    End If
  Next varValue
  If Not insertValues = "" Then
    insertValues = insertValues & ")"
  End If
End Function
Public Function sqlTxt(varItem As Variant) As Variant
  If Not IsNull(varItem) Then
    varItem = Replace(varItem, "'", "''")
    sqlTxt = "'" & varItem & "'"
  End If
End Function
Function SQLDate(varDate As Variant) As Variant
     If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function
Public Function createInsert(tableName As String, flds As String, Vals As String) As String
  createInsert = "INSERT INTO " & tableName & " " & flds & " VALUES " & Vals
End Function

 

[MajP]

To show the utility, I passed two more field names and a literal number plus a number stored in a variable

Public Sub Test()
  Dim flds As String
  Dim Vals As String
  Dim strSqlInsert As String
  Dim lngInStock As Long
  'demoing a number literal and number in a variable
  lngInStock = 2500
  flds = insertFields("FirstName", "LastName", "OrderID", "OrderDate", "OrderQuantity", "InStock")
  Vals = insertValues(sqlTxt("Mike"), sqlTxt("O'Brien"), Null, SQLDate(Date), 100, lngInStock)
  strSqlInsert = createInsert("MyTableName", flds, Vals)
  Debug.Print strSqlInsert
End Sub

INSERT INTO MyTableName ([FirstName], [LastName], [OrderID], [OrderDate], [OrderQuantity], [InStock]) VALUES ('Mike', 'O''Brien', NULL, #11/22/2013#, 100, 2500)

 

[jpl458]

MajP, trying to get the helper up and running, but have a problem. I've created all the functions
Here's the code I use:
Dim flds As String
Dim Vals As String
Dim strSqlInsert As String

flds = insertFields("Account_Number", "FirstName")
Vals = insertValues(sqlTxt("BallsRs.Fields(0).Value"), sqlTxt("BallsRs.Fields(1).Value"))
strSqlInsert = createInsert("BallsTable", flds, Vals)
Debug.Print strSqlInsert
DoCmd.RunSQL strSqlInsert

The BallsTable then looks like this:
Account_Number FirstName
BallsRs.Fields(0).Va BallsRs.Fields(1).Value
BallsRs.Fields(0).Va BallsRs.Fields(1).Value

The Debug.Print strSqlInsert looks like this.
INSERT INTO BallsTable ([Account_Number], [FirstName]) VALUES ('BallsRs.Fields(0).Value', 'BallsRs.Fields(1).Value')

I must be missing something. It's really cool, very clever.
jpl

 

[jpl458]

Well, I can actually say this in the proper context --- I've cracked the code.

Here is that code"

Dim flds As String
Dim Vals As String
Dim strSqlInsert As String

flds = insertFields("Account_Number", "FirstName", "LastName", "Date_of_Transaction")
Vals = insertValues(sqlTxt(BallsRs.Fields(0).Value), sqlTxt(BallsRs.Fields(1).Value), sqlTxt(BallsRs.Fields(2).Value), _
SQLDate(BallsRs.Fields(3).Value))
strSqlInsert = createInsert("BallsTable", flds, Vals)
Debug.Print strSqlInsert
DoCmd.RunSQL strSqlInsert

It's more than a bit easier than doing it all by hand.

Thanks to all, and to MajP for the helper.

jpl

 

[jpl458]

MajP;
In trying to add a numeric field to the helper there is a problem adding a variable that is a number. It runs great till I try to add the vaiable WkDayNo which is BallsRs.Fields(8).Value which is a n element in a record set.
But if I create a variable dim rstext as Long
rstest = BallsRs.Fields(8).Value and insert rstest into the vals in inserts ok into the table. I must be missing the way of defining the element in the record set with [] or something. Tried all sorts of combinations none of which work.

flds = insertFields("Account_Number", "FirstName", "LastName", "Date_of_Transaction", "Account_Type_2", _
"CYear", "CMonth", "Dayofwk", "WkDayNo")
Vals = insertValues(sqlTxt(BallsRs.Fields(0).Value), sqlTxt(BallsRs.Fields(1).Value), sqlTxt(BallsRs.Fields(2).Value), _
SQLDate(BallsRs.Fields(3).Value), sqlTxt(BallsRs.Fields(4).Value), sqlTxt(BallsRs.Fields(5).Value), sqlTxt(BallsRs.Fields(6).Value), _
sqlTxt(BallsRs.Fields(7).Value), BallsRs.Fields(8).Value)
strSqlInsert = createInsert("BallsTable", flds, Vals)

Thanks in advance

jpl

 

[MajP]

Sorry, I cannot really understand what you are saying. It is a little confusing. To error check, I would do something more like this.

dim AcctNum as variant
dim FirstName as variant
dim lastName as variant
dim DofT as variant
dim AcctType as variant
dim Cyear as variant
dim CMonth as variant
dim DayOfWeek as variant
dim WkDayNo as variant

acctNum = sqlTxt(BallsRs.Fields(0).Value)
FirstName = sqlTxt(BallsRs.Fields(1).Value)
lastName = sqlTxt(BallsRs.Fields(2).Value)
DofT = SQLDate(BallsRs.Fields(3).Value)
AcctType = sqlTxt(BallsRs.Fields(4).Value)
Cyear = sqlTxt(BallsRs.Fields(5).Value)
Cmonth = sqlTxt(BallsRs.Fields(6).Value)
DayOfWeek = sqlTxt(BallsRs.Fields(7).Value)
WkDayNo = BallsRs.Fields(8).Value

flds = insertFields("Account_Number", "FirstName", "LastName", "Date_of_Transaction", "Account_Type_2", _
"CYear", "CMonth", "Dayofwk", "WkDayNo")

Debug.print flds ' verify this looks good

vals = insertValues(acctNum, firstName, lastName, DofT, AcctType, Cyear, Cmonth, DayofWeek, WkDayNo)

debug.print vals ' Verify this

strSqlInsert = createInsert("BallsTable", flds, Vals)

debug.print strSqlInsert

Please post the results from the immediate window.

 

[Andrzejek]

I looked at you original post and have to ask: when you said “Want to insert a record from a recordset into and existing empty ACCESS table”, do you create a recordset from the info in Access?

In another words, is it something like:[tt][blue]
Select Comments, DOB, Salary
From SomeOldTable
Where ...[/blue][/tt]

If so, do you Insert all records from your recordset into this ‘existing empty ACCESS table’? If so, creating the recordset is not necessary – you may simply do:
[tt]
INSERT INTO MyEmptyTable (TextField, DateField, NumberField)[blue]
Select Comments, DOB, Salary
From SomeOldTable
Where [/blue][/tt]

And you are done. No loops, no messing with SQLs

Have fun.

---- Andy

 

[jpl458]

MajP, I tried your test and got a ByRef type mismatch on flds.

I tried this and ACCESS says there the number of fields does not match But I count 20 on both sides

Dim insertStrng As String
insertSrng = "INSERT INTO BallsTable ([Account_Number], [FirstName], [LastName], [Date_of_Transaction], [Account_Type_2], [CYear], [CMonth], [Dayofwk], [WkDayNo], [FYr], [FMo], "
insertSrng = insertSrng + "[WkDay], [WeekNum], [MoDay], [Location_Id], [StartTime], [EndTime], [Duration], [Hour], [Balls]) "
insertSrng = insertSrng + "VALUES ('BallsRs.Fields(0).Value', 'BallsRs.Fields(1).Value', 'BallsRs.Fields(2).Value', #BallsRs.Fields(3).Value#, 'BallsRs.Fields(4).Value', "
insertSrng = insertSrng + "'BallsRs.Fields(5).Value', 'BallsRs.Fields(6).Value', 'BallsRs.Fields(7).Value', BallsRs.Fields(8).Value, BallsRs.Fields(9).Value, BallsRs.Fields(10).Value, "
insertSrng = insertSrng + "BallsRs.Fields(11).Value, BallsRs.Fields(12).Value, BallsRs.Fields(13).Value, 'BallsRs.Fields(14).Value', #BallsRs.Fields(15).Value#, "
insertSrng = insertSrng + "#BallsRs.Fields(16).Value#, BallsRs.Fields(17).Value, BallsRs.Fields(18).Value), BallsRs.Fields(19).Value"
DoCmd.RunSQL insertSrng

I really appreciate your time on this
Hope I am not wearing out my welcome.

jpl

 

[dhookom]

You Dim a variable but then use a different, un-Dimmed variable:

Dim [b][COLOR=#EF2929]insertStrng[/color][/b] As String
[b][COLOR=#EF2929]insertSrng[/color][/b] = ...

Try insert a debug line

insertSrng = insertSrng + "#BallsRs.Fields(16).Value#, BallsRs.Fields(17).Value, BallsRs.Fields(18).Value), BallsRs.Fields(19).Value"
[b][COLOR=#EF2929]Debug.Print insertSrng[/color][/b]
DoCmd.RunSQL insertSrng

If you can't use this to figure out why the error then come back with the results of the debug.print.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

JPL,
You need to learn how to debug your own code. Read up on this. There is a lot on the web. You are starting to waste people time, you are asking for a whole lot of hand holding. People on this site will bend over backward to help, but you need to be able to contribute.

 

[dhookom]

Check out faq705-7148.

Duane
Hook'D on Access
MS Access MVP