Transfering data between External SQL and MS Database - using VBA in EXCEL

  • Thread starter Thread starter Pete Straman Straman via AccessMonster.com
  • Start date Start date
P

Pete Straman Straman via AccessMonster.com

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
(e-mail address removed) - please feel free to forward direct.
 
It's really easy: just use the following format on any fields you think may
have a single quote in them: Where str1a is the new string to insert in your
SQL statement and str1 is the original. There is also an example in SQL 2k
books online search under "single quote" choose the topic on "set
Quoted_Identifier."

str1a = replace (str1,"'","''")

Pete Straman Straman via AccessMonster.c said:
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
(e-mail address removed) - please feel free to forward direct.
Click to expand...
 
Thanks Andrew, that was easy and a big help. I do not know why it works. I
will try to find out later. Onwward to the next stumbling block I go.

C. Pete Straman
 
Back
Top