PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Access > Microsoft Access VBA Modules > Array to SQL question

Reply
Thread Tools Rate Thread

Array to SQL question

 
 
=?Utf-8?B?QW5naWU=?=
Guest
Posts: n/a
 
      12th Nov 2007
I have 41 tables. I would like to append the data in them to one table. I
don't work with arrays much, but would like to get around writing tons of
queries. Can someone take a look at this and tell me what I am doing wrong?

Public Sub FillArrayandAppend()
covs(0) = "COV000 MTD Application Summaryb"
covs(1) = "COV001 MTD Application Summaryb"
covs(2) = "COV002 MTD Application Summaryb"
covs(3) = "COV003 MTD Application Summaryb"
covs(4) = "COV004 MTD Application Summaryb"
covs(5) = "COV005 MTD Application Summaryb"
covs(6) = "COV006 MTD Application Summaryb"
covs(7) = "COV099 MTD Application Summaryb"
covs(8) = "COV100 MTD Application Summary"
covs(9) = "COV101 MTD Application Summary"
covs(10) = "COV104 MTD Application Summary"
covs(11) = "COV106 MTD Application Summary"
covs(12) = "COV201 MTD Application Summary"
covs(13) = "COV202 MTD Application Summary"
covs(14) = "COV211 MTD Application Summary"
covs(15) = "COV213 MTD Application Summary"
covs(16) = "COV301 MTD Application Summary"
covs(17) = "COV302 MTD Application Summary"
covs(18) = "COV303 MTD Application Summary"
covs(19) = "COV304 MTD Application Summary"
covs(20) = "COV600 MTD Application Summary"
covs(21) = "COV610 MTD Application Summary"
covs(22) = "COV611 MTD Application Summary"
covs(23) = "COV613 MTD Application Summary"
covs(24) = "COV614 MTD Application Summary"
covs(25) = "COV615 MTD Application Summary"
covs(26) = "COV617 MTD Application Summary"
covs(27) = "COV700 MTD Application Summary"
covs(28) = "COV701 MTD Application Summary"
covs(29) = "COV702 MTD Application Summary"
covs(30) = "COV703 MTD Application Summary"
covs(31) = "COV708 MTD Application Summary"
covs(32) = "COV709 MTD Application Summary"
covs(33) = "COV710 MTD Application Summary"
covs(34) = "COV798 MTD Application Summary"
covs(35) = "COV799 MTD Application Summary"
covs(36) = "COV801 MTD Application Summary"
covs(37) = "COV803 MTD Application Summary"
covs(38) = "COV804 MTD Application Summary"
covs(39) = "COV805 MTD Application Summary"
covs(40) = "COV990 MTD Application Summary"
covs(41) = "COV992 MTD Application Summary"

For x = 0 To 41
Dim db As DAO.Database
Dim rst As DAO.Recordset
strtable = covs(x)
Set db = CurrentDb
sqlstatement = "Select * From [" & strtable & "]"
Set rst = db.OpenRecordset(sqlstatement)


ssql = "INSERT INTO MonthlyDetail ( [Item Code], [Item Description],
Units, Price, Extension, AppCode, DateOfFile ) SELECT rst.F1, rst.F2, rst.F3,
rst.F4, rst.F5, Left(rst,6) AS AppCode, [Date of File] AS DateOfFile FROM rst
WHERE rst.F5 Is Not Null;"
DoCmd.RunSQL ssql
Set rst = Nothing
Set db = Nothing

Next

End Sub
 
Reply With Quote
 
 
 
 
Douglas J. Steele
Guest
Posts: n/a
 
      12th Nov 2007
There's no need for the recordset:

Dim db As DAO.Database

Set db = CurrentDb

For x = 0 To 41
strtable = covs(x)
ssql = "INSERT INTO MonthlyDetail ( [Item Code], [Item Description], " &
_
"Units, Price, Extension, AppCode, DateOfFile ) " & _
"SELECT F1, F2, F3, F4, F5, '" & _
Left(strTable,6) & "' AS AppCode, " & _
"[Date of File] AS DateOfFile FROM " & strtable & _
" WHERE rst.F5 Is Not Null;"
db.Execute ssql, dbFailOnError
Next x

I'm not sure what [Date of File] is supposed to be.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Angie" <(E-Mail Removed)> wrote in message
news:68ECE87E-F6AF-4DE4-8BBE-(E-Mail Removed)...
>I have 41 tables. I would like to append the data in them to one table. I
> don't work with arrays much, but would like to get around writing tons of
> queries. Can someone take a look at this and tell me what I am doing
> wrong?
>
> Public Sub FillArrayandAppend()
> covs(0) = "COV000 MTD Application Summaryb"
> covs(1) = "COV001 MTD Application Summaryb"
> covs(2) = "COV002 MTD Application Summaryb"
> covs(3) = "COV003 MTD Application Summaryb"
> covs(4) = "COV004 MTD Application Summaryb"
> covs(5) = "COV005 MTD Application Summaryb"
> covs(6) = "COV006 MTD Application Summaryb"
> covs(7) = "COV099 MTD Application Summaryb"
> covs(8) = "COV100 MTD Application Summary"
> covs(9) = "COV101 MTD Application Summary"
> covs(10) = "COV104 MTD Application Summary"
> covs(11) = "COV106 MTD Application Summary"
> covs(12) = "COV201 MTD Application Summary"
> covs(13) = "COV202 MTD Application Summary"
> covs(14) = "COV211 MTD Application Summary"
> covs(15) = "COV213 MTD Application Summary"
> covs(16) = "COV301 MTD Application Summary"
> covs(17) = "COV302 MTD Application Summary"
> covs(18) = "COV303 MTD Application Summary"
> covs(19) = "COV304 MTD Application Summary"
> covs(20) = "COV600 MTD Application Summary"
> covs(21) = "COV610 MTD Application Summary"
> covs(22) = "COV611 MTD Application Summary"
> covs(23) = "COV613 MTD Application Summary"
> covs(24) = "COV614 MTD Application Summary"
> covs(25) = "COV615 MTD Application Summary"
> covs(26) = "COV617 MTD Application Summary"
> covs(27) = "COV700 MTD Application Summary"
> covs(28) = "COV701 MTD Application Summary"
> covs(29) = "COV702 MTD Application Summary"
> covs(30) = "COV703 MTD Application Summary"
> covs(31) = "COV708 MTD Application Summary"
> covs(32) = "COV709 MTD Application Summary"
> covs(33) = "COV710 MTD Application Summary"
> covs(34) = "COV798 MTD Application Summary"
> covs(35) = "COV799 MTD Application Summary"
> covs(36) = "COV801 MTD Application Summary"
> covs(37) = "COV803 MTD Application Summary"
> covs(38) = "COV804 MTD Application Summary"
> covs(39) = "COV805 MTD Application Summary"
> covs(40) = "COV990 MTD Application Summary"
> covs(41) = "COV992 MTD Application Summary"
>
> For x = 0 To 41
> Dim db As DAO.Database
> Dim rst As DAO.Recordset
> strtable = covs(x)
> Set db = CurrentDb
> sqlstatement = "Select * From [" & strtable & "]"
> Set rst = db.OpenRecordset(sqlstatement)
>
>
> ssql = "INSERT INTO MonthlyDetail ( [Item Code], [Item Description],
> Units, Price, Extension, AppCode, DateOfFile ) SELECT rst.F1, rst.F2,
> rst.F3,
> rst.F4, rst.F5, Left(rst,6) AS AppCode, [Date of File] AS DateOfFile FROM
> rst
> WHERE rst.F5 Is Not Null;"
> DoCmd.RunSQL ssql
> Set rst = Nothing
> Set db = Nothing
>
> Next
>
> End Sub
 
Reply With Quote
 
=?Utf-8?B?QW5naWU=?=
Guest
Posts: n/a
 
      12th Nov 2007
Thank you so much! I get a 3131 error though "Syntax error in FROM clause"

"Douglas J. Steele" wrote:

> There's no need for the recordset:
>
> Dim db As DAO.Database
>
> Set db = CurrentDb
>
> For x = 0 To 41
> strtable = covs(x)
> ssql = "INSERT INTO MonthlyDetail ( [Item Code], [Item Description], " &
> _
> "Units, Price, Extension, AppCode, DateOfFile ) " & _
> "SELECT F1, F2, F3, F4, F5, '" & _
> Left(strTable,6) & "' AS AppCode, " & _
> "[Date of File] AS DateOfFile FROM " & strtable & _
> " WHERE rst.F5 Is Not Null;"
> db.Execute ssql, dbFailOnError
> Next x
>
> I'm not sure what [Date of File] is supposed to be.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Angie" <(E-Mail Removed)> wrote in message
> news:68ECE87E-F6AF-4DE4-8BBE-(E-Mail Removed)...
> >I have 41 tables. I would like to append the data in them to one table. I
> > don't work with arrays much, but would like to get around writing tons of
> > queries. Can someone take a look at this and tell me what I am doing
> > wrong?
> >
> > Public Sub FillArrayandAppend()
> > covs(0) = "COV000 MTD Application Summaryb"
> > covs(1) = "COV001 MTD Application Summaryb"
> > covs(2) = "COV002 MTD Application Summaryb"
> > covs(3) = "COV003 MTD Application Summaryb"
> > covs(4) = "COV004 MTD Application Summaryb"
> > covs(5) = "COV005 MTD Application Summaryb"
> > covs(6) = "COV006 MTD Application Summaryb"
> > covs(7) = "COV099 MTD Application Summaryb"
> > covs(8) = "COV100 MTD Application Summary"
> > covs(9) = "COV101 MTD Application Summary"
> > covs(10) = "COV104 MTD Application Summary"
> > covs(11) = "COV106 MTD Application Summary"
> > covs(12) = "COV201 MTD Application Summary"
> > covs(13) = "COV202 MTD Application Summary"
> > covs(14) = "COV211 MTD Application Summary"
> > covs(15) = "COV213 MTD Application Summary"
> > covs(16) = "COV301 MTD Application Summary"
> > covs(17) = "COV302 MTD Application Summary"
> > covs(18) = "COV303 MTD Application Summary"
> > covs(19) = "COV304 MTD Application Summary"
> > covs(20) = "COV600 MTD Application Summary"
> > covs(21) = "COV610 MTD Application Summary"
> > covs(22) = "COV611 MTD Application Summary"
> > covs(23) = "COV613 MTD Application Summary"
> > covs(24) = "COV614 MTD Application Summary"
> > covs(25) = "COV615 MTD Application Summary"
> > covs(26) = "COV617 MTD Application Summary"
> > covs(27) = "COV700 MTD Application Summary"
> > covs(28) = "COV701 MTD Application Summary"
> > covs(29) = "COV702 MTD Application Summary"
> > covs(30) = "COV703 MTD Application Summary"
> > covs(31) = "COV708 MTD Application Summary"
> > covs(32) = "COV709 MTD Application Summary"
> > covs(33) = "COV710 MTD Application Summary"
> > covs(34) = "COV798 MTD Application Summary"
> > covs(35) = "COV799 MTD Application Summary"
> > covs(36) = "COV801 MTD Application Summary"
> > covs(37) = "COV803 MTD Application Summary"
> > covs(38) = "COV804 MTD Application Summary"
> > covs(39) = "COV805 MTD Application Summary"
> > covs(40) = "COV990 MTD Application Summary"
> > covs(41) = "COV992 MTD Application Summary"
> >
> > For x = 0 To 41
> > Dim db As DAO.Database
> > Dim rst As DAO.Recordset
> > strtable = covs(x)
> > Set db = CurrentDb
> > sqlstatement = "Select * From [" & strtable & "]"
> > Set rst = db.OpenRecordset(sqlstatement)
> >
> >
> > ssql = "INSERT INTO MonthlyDetail ( [Item Code], [Item Description],
> > Units, Price, Extension, AppCode, DateOfFile ) SELECT rst.F1, rst.F2,
> > rst.F3,
> > rst.F4, rst.F5, Left(rst,6) AS AppCode, [Date of File] AS DateOfFile FROM
> > rst
> > WHERE rst.F5 Is Not Null;"
> > DoCmd.RunSQL ssql
> > Set rst = Nothing
> > Set db = Nothing
> >
> > Next
> >
> > End Sub
>
>
>
 
Reply With Quote
 
John Spencer
Guest
Posts: n/a
 
      12th Nov 2007
Douglas Steele missed that your table names contains spaces and
therefore requires square brackets to be placed around the name of the
table(s).

ssql = "INSERT INTO MonthlyDetail " & _
( [Item Code], [Item Description], " & _
"Units, Price, Extension, AppCode, DateOfFile ) " & _
"SELECT F1, F2, F3, F4, F5, '" & _
Left(strTable,6) & "' AS AppCode, " & _
"[Date of File] AS DateOfFile FROM [" & strtable & _
"] WHERE rst.F5 Is Not Null;"

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Angie wrote:
> Thank you so much! I get a 3131 error though "Syntax error in FROM clause"
>
> "Douglas J. Steele" wrote:
>
>> There's no need for the recordset:
>>
>> Dim db As DAO.Database
>>
>> Set db = CurrentDb
>>
>> For x = 0 To 41
>> strtable = covs(x)
>> ssql = "INSERT INTO MonthlyDetail ( [Item Code], [Item Description], " &
>> _
>> "Units, Price, Extension, AppCode, DateOfFile ) " & _
>> "SELECT F1, F2, F3, F4, F5, '" & _
>> Left(strTable,6) & "' AS AppCode, " & _
>> "[Date of File] AS DateOfFile FROM " & strtable & _
>> " WHERE rst.F5 Is Not Null;"
>> db.Execute ssql, dbFailOnError
>> Next x
>>
>> I'm not sure what [Date of File] is supposed to be.
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "Angie" <(E-Mail Removed)> wrote in message
>> news:68ECE87E-F6AF-4DE4-8BBE-(E-Mail Removed)...
>>> I have 41 tables. I would like to append the data in them to one table. I
>>> don't work with arrays much, but would like to get around writing tons of
>>> queries. Can someone take a look at this and tell me what I am doing
>>> wrong?
>>>
>>> Public Sub FillArrayandAppend()
>>> covs(0) = "COV000 MTD Application Summaryb"
>>> covs(1) = "COV001 MTD Application Summaryb"
>>> covs(2) = "COV002 MTD Application Summaryb"
>>> covs(3) = "COV003 MTD Application Summaryb"
>>> covs(4) = "COV004 MTD Application Summaryb"
>>> covs(5) = "COV005 MTD Application Summaryb"
>>> covs(6) = "COV006 MTD Application Summaryb"
>>> covs(7) = "COV099 MTD Application Summaryb"
>>> covs(8) = "COV100 MTD Application Summary"
>>> covs(9) = "COV101 MTD Application Summary"
>>> covs(10) = "COV104 MTD Application Summary"
>>> covs(11) = "COV106 MTD Application Summary"
>>> covs(12) = "COV201 MTD Application Summary"
>>> covs(13) = "COV202 MTD Application Summary"
>>> covs(14) = "COV211 MTD Application Summary"
>>> covs(15) = "COV213 MTD Application Summary"
>>> covs(16) = "COV301 MTD Application Summary"
>>> covs(17) = "COV302 MTD Application Summary"
>>> covs(18) = "COV303 MTD Application Summary"
>>> covs(19) = "COV304 MTD Application Summary"
>>> covs(20) = "COV600 MTD Application Summary"
>>> covs(21) = "COV610 MTD Application Summary"
>>> covs(22) = "COV611 MTD Application Summary"
>>> covs(23) = "COV613 MTD Application Summary"
>>> covs(24) = "COV614 MTD Application Summary"
>>> covs(25) = "COV615 MTD Application Summary"
>>> covs(26) = "COV617 MTD Application Summary"
>>> covs(27) = "COV700 MTD Application Summary"
>>> covs(28) = "COV701 MTD Application Summary"
>>> covs(29) = "COV702 MTD Application Summary"
>>> covs(30) = "COV703 MTD Application Summary"
>>> covs(31) = "COV708 MTD Application Summary"
>>> covs(32) = "COV709 MTD Application Summary"
>>> covs(33) = "COV710 MTD Application Summary"
>>> covs(34) = "COV798 MTD Application Summary"
>>> covs(35) = "COV799 MTD Application Summary"
>>> covs(36) = "COV801 MTD Application Summary"
>>> covs(37) = "COV803 MTD Application Summary"
>>> covs(38) = "COV804 MTD Application Summary"
>>> covs(39) = "COV805 MTD Application Summary"
>>> covs(40) = "COV990 MTD Application Summary"
>>> covs(41) = "COV992 MTD Application Summary"
>>>
>>> For x = 0 To 41
>>> Dim db As DAO.Database
>>> Dim rst As DAO.Recordset
>>> strtable = covs(x)
>>> Set db = CurrentDb
>>> sqlstatement = "Select * From [" & strtable & "]"
>>> Set rst = db.OpenRecordset(sqlstatement)
>>>
>>>
>>> ssql = "INSERT INTO MonthlyDetail ( [Item Code], [Item Description],
>>> Units, Price, Extension, AppCode, DateOfFile ) SELECT rst.F1, rst.F2,
>>> rst.F3,
>>> rst.F4, rst.F5, Left(rst,6) AS AppCode, [Date of File] AS DateOfFile FROM
>>> rst
>>> WHERE rst.F5 Is Not Null;"
>>> DoCmd.RunSQL ssql
>>> Set rst = Nothing
>>> Set db = Nothing
>>>
>>> Next
>>>
>>> End Sub
>>
>>
 
Reply With Quote
 
Douglas J. Steele
Guest
Posts: n/a
 
      13th Nov 2007
Thanks, John.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"John Spencer" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Douglas Steele missed that your table names contains spaces and therefore
> requires square brackets to be placed around the name of the table(s).
>
> ssql = "INSERT INTO MonthlyDetail " & _
> ( [Item Code], [Item Description], " & _
> "Units, Price, Extension, AppCode, DateOfFile ) " & _
> "SELECT F1, F2, F3, F4, F5, '" & _
> Left(strTable,6) & "' AS AppCode, " & _
> "[Date of File] AS DateOfFile FROM [" & strtable & _
> "] WHERE rst.F5 Is Not Null;"
>
> '====================================================
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> '====================================================
>
>
> Angie wrote:
>> Thank you so much! I get a 3131 error though "Syntax error in FROM
>> clause"
>>
>> "Douglas J. Steele" wrote:
>>
>>> There's no need for the recordset:
>>>
>>> Dim db As DAO.Database
>>>
>>> Set db = CurrentDb
>>>
>>> For x = 0 To 41
>>> strtable = covs(x)
>>> ssql = "INSERT INTO MonthlyDetail ( [Item Code], [Item Description],
>>> " & _
>>> "Units, Price, Extension, AppCode, DateOfFile ) " & _
>>> "SELECT F1, F2, F3, F4, F5, '" & _
>>> Left(strTable,6) & "' AS AppCode, " & _
>>> "[Date of File] AS DateOfFile FROM " & strtable & _
>>> " WHERE rst.F5 Is Not Null;"
>>> db.Execute ssql, dbFailOnError
>>> Next x
>>>
>>> I'm not sure what [Date of File] is supposed to be.
>>>
>>> --
>>> Doug Steele, Microsoft Access MVP
>>> http://I.Am/DougSteele
>>> (no e-mails, please!)
>>>
>>>
>>> "Angie" <(E-Mail Removed)> wrote in message
>>> news:68ECE87E-F6AF-4DE4-8BBE-(E-Mail Removed)...
>>>> I have 41 tables. I would like to append the data in them to one table.
>>>> I
>>>> don't work with arrays much, but would like to get around writing tons
>>>> of
>>>> queries. Can someone take a look at this and tell me what I am doing
>>>> wrong?
>>>>
>>>> Public Sub FillArrayandAppend()
>>>> covs(0) = "COV000 MTD Application Summaryb"
>>>> covs(1) = "COV001 MTD Application Summaryb"
>>>> covs(2) = "COV002 MTD Application Summaryb"
>>>> covs(3) = "COV003 MTD Application Summaryb"
>>>> covs(4) = "COV004 MTD Application Summaryb"
>>>> covs(5) = "COV005 MTD Application Summaryb"
>>>> covs(6) = "COV006 MTD Application Summaryb"
>>>> covs(7) = "COV099 MTD Application Summaryb"
>>>> covs(8) = "COV100 MTD Application Summary"
>>>> covs(9) = "COV101 MTD Application Summary"
>>>> covs(10) = "COV104 MTD Application Summary"
>>>> covs(11) = "COV106 MTD Application Summary"
>>>> covs(12) = "COV201 MTD Application Summary"
>>>> covs(13) = "COV202 MTD Application Summary"
>>>> covs(14) = "COV211 MTD Application Summary"
>>>> covs(15) = "COV213 MTD Application Summary"
>>>> covs(16) = "COV301 MTD Application Summary"
>>>> covs(17) = "COV302 MTD Application Summary"
>>>> covs(18) = "COV303 MTD Application Summary"
>>>> covs(19) = "COV304 MTD Application Summary"
>>>> covs(20) = "COV600 MTD Application Summary"
>>>> covs(21) = "COV610 MTD Application Summary"
>>>> covs(22) = "COV611 MTD Application Summary"
>>>> covs(23) = "COV613 MTD Application Summary"
>>>> covs(24) = "COV614 MTD Application Summary"
>>>> covs(25) = "COV615 MTD Application Summary"
>>>> covs(26) = "COV617 MTD Application Summary"
>>>> covs(27) = "COV700 MTD Application Summary"
>>>> covs(28) = "COV701 MTD Application Summary"
>>>> covs(29) = "COV702 MTD Application Summary"
>>>> covs(30) = "COV703 MTD Application Summary"
>>>> covs(31) = "COV708 MTD Application Summary"
>>>> covs(32) = "COV709 MTD Application Summary"
>>>> covs(33) = "COV710 MTD Application Summary"
>>>> covs(34) = "COV798 MTD Application Summary"
>>>> covs(35) = "COV799 MTD Application Summary"
>>>> covs(36) = "COV801 MTD Application Summary"
>>>> covs(37) = "COV803 MTD Application Summary"
>>>> covs(38) = "COV804 MTD Application Summary"
>>>> covs(39) = "COV805 MTD Application Summary"
>>>> covs(40) = "COV990 MTD Application Summary"
>>>> covs(41) = "COV992 MTD Application Summary"
>>>>
>>>> For x = 0 To 41
>>>> Dim db As DAO.Database
>>>> Dim rst As DAO.Recordset
>>>> strtable = covs(x)
>>>> Set db = CurrentDb
>>>> sqlstatement = "Select * From [" & strtable & "]"
>>>> Set rst = db.OpenRecordset(sqlstatement)
>>>>
>>>>
>>>> ssql = "INSERT INTO MonthlyDetail ( [Item Code], [Item
>>>> Description],
>>>> Units, Price, Extension, AppCode, DateOfFile ) SELECT rst.F1, rst.F2,
>>>> rst.F3,
>>>> rst.F4, rst.F5, Left(rst,6) AS AppCode, [Date of File] AS DateOfFile
>>>> FROM rst
>>>> WHERE rst.F5 Is Not Null;"
>>>> DoCmd.RunSQL ssql
>>>> Set rst = Nothing
>>>> Set db = Nothing
>>>>
>>>> Next
>>>>
>>>> End Sub
>>>
>>>
 
Reply With Quote
 
=?Utf-8?B?QW5naWU=?=
Guest
Posts: n/a
 
      13th Nov 2007
Thanks guys so much, it works!

"Douglas J. Steele" wrote:

> Thanks, John.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "John Spencer" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Douglas Steele missed that your table names contains spaces and therefore
> > requires square brackets to be placed around the name of the table(s).
> >
> > ssql = "INSERT INTO MonthlyDetail " & _
> > ( [Item Code], [Item Description], " & _
> > "Units, Price, Extension, AppCode, DateOfFile ) " & _
> > "SELECT F1, F2, F3, F4, F5, '" & _
> > Left(strTable,6) & "' AS AppCode, " & _
> > "[Date of File] AS DateOfFile FROM [" & strtable & _
> > "] WHERE rst.F5 Is Not Null;"
> >
> > '====================================================
> > John Spencer
> > Access MVP 2002-2005, 2007
> > Center for Health Program Development and Management
> > University of Maryland Baltimore County
> > '====================================================
> >
> >
> > Angie wrote:
> >> Thank you so much! I get a 3131 error though "Syntax error in FROM
> >> clause"
> >>
> >> "Douglas J. Steele" wrote:
> >>
> >>> There's no need for the recordset:
> >>>
> >>> Dim db As DAO.Database
> >>>
> >>> Set db = CurrentDb
> >>>
> >>> For x = 0 To 41
> >>> strtable = covs(x)
> >>> ssql = "INSERT INTO MonthlyDetail ( [Item Code], [Item Description],
> >>> " & _
> >>> "Units, Price, Extension, AppCode, DateOfFile ) " & _
> >>> "SELECT F1, F2, F3, F4, F5, '" & _
> >>> Left(strTable,6) & "' AS AppCode, " & _
> >>> "[Date of File] AS DateOfFile FROM " & strtable & _
> >>> " WHERE rst.F5 Is Not Null;"
> >>> db.Execute ssql, dbFailOnError
> >>> Next x
> >>>
> >>> I'm not sure what [Date of File] is supposed to be.
> >>>
> >>> --
> >>> Doug Steele, Microsoft Access MVP
> >>> http://I.Am/DougSteele
> >>> (no e-mails, please!)
> >>>
> >>>
> >>> "Angie" <(E-Mail Removed)> wrote in message
> >>> news:68ECE87E-F6AF-4DE4-8BBE-(E-Mail Removed)...
> >>>> I have 41 tables. I would like to append the data in them to one table.
> >>>> I
> >>>> don't work with arrays much, but would like to get around writing tons
> >>>> of
> >>>> queries. Can someone take a look at this and tell me what I am doing
> >>>> wrong?
> >>>>
> >>>> Public Sub FillArrayandAppend()
> >>>> covs(0) = "COV000 MTD Application Summaryb"
> >>>> covs(1) = "COV001 MTD Application Summaryb"
> >>>> covs(2) = "COV002 MTD Application Summaryb"
> >>>> covs(3) = "COV003 MTD Application Summaryb"
> >>>> covs(4) = "COV004 MTD Application Summaryb"
> >>>> covs(5) = "COV005 MTD Application Summaryb"
> >>>> covs(6) = "COV006 MTD Application Summaryb"
> >>>> covs(7) = "COV099 MTD Application Summaryb"
> >>>> covs(8) = "COV100 MTD Application Summary"
> >>>> covs(9) = "COV101 MTD Application Summary"
> >>>> covs(10) = "COV104 MTD Application Summary"
> >>>> covs(11) = "COV106 MTD Application Summary"
> >>>> covs(12) = "COV201 MTD Application Summary"
> >>>> covs(13) = "COV202 MTD Application Summary"
> >>>> covs(14) = "COV211 MTD Application Summary"
> >>>> covs(15) = "COV213 MTD Application Summary"
> >>>> covs(16) = "COV301 MTD Application Summary"
> >>>> covs(17) = "COV302 MTD Application Summary"
> >>>> covs(18) = "COV303 MTD Application Summary"
> >>>> covs(19) = "COV304 MTD Application Summary"
> >>>> covs(20) = "COV600 MTD Application Summary"
> >>>> covs(21) = "COV610 MTD Application Summary"
> >>>> covs(22) = "COV611 MTD Application Summary"
> >>>> covs(23) = "COV613 MTD Application Summary"
> >>>> covs(24) = "COV614 MTD Application Summary"
> >>>> covs(25) = "COV615 MTD Application Summary"
> >>>> covs(26) = "COV617 MTD Application Summary"
> >>>> covs(27) = "COV700 MTD Application Summary"
> >>>> covs(28) = "COV701 MTD Application Summary"
> >>>> covs(29) = "COV702 MTD Application Summary"
> >>>> covs(30) = "COV703 MTD Application Summary"
> >>>> covs(31) = "COV708 MTD Application Summary"
> >>>> covs(32) = "COV709 MTD Application Summary"
> >>>> covs(33) = "COV710 MTD Application Summary"
> >>>> covs(34) = "COV798 MTD Application Summary"
> >>>> covs(35) = "COV799 MTD Application Summary"
> >>>> covs(36) = "COV801 MTD Application Summary"
> >>>> covs(37) = "COV803 MTD Application Summary"
> >>>> covs(38) = "COV804 MTD Application Summary"
> >>>> covs(39) = "COV805 MTD Application Summary"
> >>>> covs(40) = "COV990 MTD Application Summary"
> >>>> covs(41) = "COV992 MTD Application Summary"
> >>>>
> >>>> For x = 0 To 41
> >>>> Dim db As DAO.Database
> >>>> Dim rst As DAO.Recordset
> >>>> strtable = covs(x)
> >>>> Set db = CurrentDb
> >>>> sqlstatement = "Select * From [" & strtable & "]"
> >>>> Set rst = db.OpenRecordset(sqlstatement)
> >>>>
> >>>>
> >>>> ssql = "INSERT INTO MonthlyDetail ( [Item Code], [Item
> >>>> Description],
> >>>> Units, Price, Extension, AppCode, DateOfFile ) SELECT rst.F1, rst.F2,
> >>>> rst.F3,
> >>>> rst.F4, rst.F5, Left(rst,6) AS AppCode, [Date of File] AS DateOfFile
> >>>> FROM rst
> >>>> WHERE rst.F5 Is Not Null;"
> >>>> DoCmd.RunSQL ssql
> >>>> Set rst = Nothing
> >>>> Set db = Nothing
> >>>>
> >>>> Next
> >>>>
> >>>> End Sub
> >>>
> >>>
>
>
>
 
Reply With Quote
 
 
 
Reply

« Customizing Ribbon - Close Print Preview Button? | Passing linked table data »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or/ reverse the original array order) Keith R Microsoft Excel Programming 3 13th Nov 2007 04:08 PM
another array question Robert Dieckmann Microsoft Excel Worksheet Functions 4 20th Jan 2007 02:08 AM
Array question Lee Microsoft Excel Programming 1 11th Dec 2003 08:01 PM
Array question Stuart Microsoft Excel Programming 1 6th Aug 2003 04:13 AM
Is this an array question? Stuart Microsoft Excel Programming 0 5th Aug 2003 08:53 PM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 04:39 PM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top