Transfering data between External SQL and MS Database - using VBA in EXCEL
- From: "Pete Straman Straman via AccessMonster.com" <forum@xxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sun, 08 May 2005 17:06:36 GMT
Hey:
I have to loop through 150 + MS Acces databases to combine them into one
SQL Server table. I am using VBA Code to find and import each table. There
is only one table within each database that I need to obtain.
Hope this purpose suffices.
My usual sql statements won't work. I made one that works some of the time
but then runs into a problem when it finds a " ' " in one of the text
fields(7).
I am working an if statement to catch it and take it out but I am not
supposed to change the data. If I input the data through the SQL Server
database it comes in just fine. Again I do not want to import 150 plus
tables manually. My code is below:
strAccessDBConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFilePath & strFileName & ";" & _
"User ID=;" & _
"Password=;"
Set cnnMSA = New ADODB.Connection
cnnMSA.Open strAccessDBConnection
Set rstMSA = New ADODB.Recordset
rstMSA.ActiveConnection = cnnMSA.ConnectionString
rstMSA.Source = (Left(strFileName, 6) & "_AUG04")
rstMSA.Open Options:=adCmdTableDirect
strSQLDBConnection = "Provider=sqloledb;Integrated Security=SSPI;Persist
Security Info=False;" & _
"Data Source=CLTL-663P351;" & _
"Initial Catalog=Diagnostic AR;"
Set cnnSQL = New ADODB.Connection
cnnSQL.Open strSQLDBConnection
Set rstSQL = New ADODB.Recordset
rstSQL.ActiveConnection = cnnSQL.ConnectionString
rstSQL.Source = ("tbl_UPTO_AUG04_HS_DATA")
rstSQL.Open Options:=adCmdTableDirect
Do Until rstMSA.EOF
**** This is what I would like to use but it will not find the
rstMSA.Source within the rstMSA.EOF?
*******************************************************************
*** commented out until the next row of "*************"
'strSQL = "INSERT INTO [" + rstSQL.Source + "]"
'+ " SELECT accountnumber AS accountnumber, transactiondate AS
transactiondate," _
'+ " financialclass AS financialclass, facilityid AS facilityid," _
'+ " visitnumber AS visitnumber, dos AS dos, facilityname AS facilityname,"
_
'+ " insname AS insname, revenue AS revenue, payment AS payment," _
'+ " adjustment AS adjustment, dosMonth AS dosMonth, dosYear AS dosYear," _
'+ " transMonth AS transMonth, transYear AS transYear, transmoyr AS
transmoyr," _
'+ " dosmoyr AS dosmoyr, facilitystate AS facilitystate" _
'+ " FROM ( Select * from " + rstMSA.Source + ";)"
****** This works until I until field 7 see a "'" in the data
*****************************************
strSQL = "INSERT INTO [" + rstSQL.Source + "]" _
+ " Values ('" & rstMSA.Fields(0) & "', '" & rstMSA.Fields(1) & "', " _
+ "'" & rstMSA.Fields(2) & "', '" & rstMSA.Fields(3) & "', " _
+ "'" & rstMSA.Fields(4) & "', '" & rstMSA.Fields(5) & "', " _
+ "'" & rstMSA.Fields(6) & "', '" & rstMSA.Fields(7) & "', " _
+ "Cast('" & rstMSA.Fields(8) & "' as money), Cast('" & rstMSA.Fields(9) &
"' as money), " _
+ "Cast('" & rstMSA.Fields(10) & "' as money), '" & rstMSA.Fields(11) & "',
" _
+ "'" & rstMSA.Fields(12) & "', '" & rstMSA.Fields(13) & "', " _
+ "'" & rstMSA.Fields(14) & "', '" & rstMSA.Fields(15) & "', " _
+ "'" & rstMSA.Fields(16) & "', '" & rstMSA.Fields(17) & "')"
cnnSQL.Execute strSQL
rstMSA.MoveNext
Loop
C. Pete Straman
pete.straman@xxxxxx - please feel free to forward direct.
--
Message posted via http://www.accessmonster.com
.
- Follow-Ups:
- RE: Transfering data between External SQL and MS Database - using VBA
- From: Andrew -ACIT Ltd
- RE: Transfering data between External SQL and MS Database - using VBA
- Prev by Date: Re: help on spilting fields?
- Next by Date: Create a Text File.............
- Previous by thread: help on spilting fields?
- Next by thread: RE: Transfering data between External SQL and MS Database - using VBA
- Index(es):
Relevant Pages
|
Loading
