Inserting excel sheet in Existing table in Access

Question

I am pretty New to VBA, In here i my tring to insert some data from the excel sheet looping through each and, in Access existing, the code runs fine but doesn't insert any data in the table, i aslo try appending that data using recordset, but did work because of the data type issue. Please guide me through it, Thank you very much in Advance. This is My Code:

Const AccessConnectionString  As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =C:\Documents and Settings\e4umts\Desktop\New Database\IRG Analytics--New.accdb;Persist Security Info=False"
Sub Import()
 Dim dbsIRG As ADODB.Connection
 Dim ConnectionString As String
 Dim IRGConn As ADODB.Connection
 Dim Mypath As String
 Dim IRGCmd As New ADODB.Command
 Dim r As Range
 Dim column As Integer
 Dim row As Integer

 Mypath = "C:\Documents and Settings\e4umts\Desktop\New Folder\Liquidation Exceptions Report.xls"
 Set IRGConn = New ADODB.Connection
 IRGConn.ConnectionString = AccessConnectionString
 IRGConn.Open
 Set IRGCmd = New ADODB.Command
 IRGCmd.ActiveConnection = IRGConn                     
 For Each r In ActiveSheet.Range("A2", Range("A2").End(xlDown))          
      If ActiveSheet.Range("A2", Range("A2").End(xlDown)) Is Nothing Then
      IRGCmd.CommandText = _
      GetSQL( _
            r.Offset(0, 0).Value, _
            r.Offset(0, 1).Value, _
            r.Offset(0, 2).Value, _
            r.Offset(0, 3).Value, _
            r.Offset(0, 4).Value, _
            r.Offset(0, 5).Value, _
            r.Offset(0, 6).Value, _
            r.Offset(0, 7).Value, _
            r.Offset(0, 8).Value, _
            r.Offset(0, 9).Value, _
            r.Offset(0, 10).Value, _
            r.Offset(0, 11).Value, _
            r.Offset(0, 12).Value, _
            r.Offset(0, 13).Value, _
            r.Offset(0, 14).Value, _
            r.Offset(0, 15).Value, _
            r.Offset(0, 16).Value, _
            r.Offset(0, 17).Value, _
            r.Offset(0, 18).Value, _
            r.Offset(0, 19).Value, _
            r.Offset(0, 20).Value, _
            r.Offset(0, 21).Value, _
            r.Offset(0, 22).Value)

        ActiveSheet.Range("A2", Range("A2").End(xlDown)).Value = ""
        IRGCmd.Execute
    Else

        End If



    Next r

    IRGConn.Close
    Set IRGConn = Nothing
 End Sub



Function GetSQL(LoanNumber As Integer, Manager As String, Analyst As String, _
   ServicerName As String, ServicerNumber As Integer, ServicerLoanNumber As Integer, _
    PoolNumber As Integer, RemmittanceType As String, SaleType As String, ActionCode As Integer, _
     ActivityDate As Date, ActionDate As Date, LPI As Date, InterestRate As Double, PandI As Double, _
       UPB As Double, ReportedPrincipal As Double, ReportedInterest As Double, AppliedPrincipal As Double, _
         AppliedInterest As Double, InvestorPassThruRate As Double, PFPIntAdv As Double, Months As Date) As String


   Dim strSql As String

    strSql = _
             " INSERT INTO Table1" & _
             " (LoanNumber, Manager, Analyst, ServicerName, ServicerNumber, ServicerLoanNumber," & _
             " PoolNumber, RemittanceType, SaleType, ActionCode, ActivityDate, ActionDate, LPI, InterestRate," & _
             " PandI, UPB, ReportedPrincipal, ReportedInterest, AppliedPrincipal, AppliedInterest, InvestorPassThruRate, PFPIntAdv, Months )" & _
             " VALUES (" & _
             " Cstr'FannieMaeLoanNumber'),'" & Manager & "','" & Analyst & "','" & ServicerName & "'," & _
             " Cstr('ServicerNumber'),Cstr('ServicerLoanNumber'), Cstr('PoolNumber'), '" & RemmittanceType & "'" & _
             " '" & SaleType & "', Cstr('ActionCode'), #" & ActivityDate & "#, #" & ActionDate & "#,#" & LPI & "#,Cstr('InterestRate')," & _
             " Cstr('PandI'),Cstr('UPB'),Cstr('ReportedPrincipal'),Cstr('ReportedInterest'),Cstr('AppliedPrincipal'),Cstr('AppliedInterest'),Cstr('InvestorPassThruRate')," & _
             " Cstr('PFPIntAdv'),#" & Months & "#)"


    GetSQL = strSql


  End Function

You are constructing an insert string but you are not executing it. You need to issue something like IRGConn.Execute strSQL to make it happen. See http://stackoverflow.com/questions/16161865/using-excel-vba-to-export-data-to-ms-access-table: — NoChance, Jan 21 '15 at 21:38
*Cstr'FannieMaeLoanNumber'),'" & Manager* - missing opening bracket after **Cstr** function. — Maciej Los, Jan 21 '15 at 22:18

score 0 · Answer 1 · answered Jan 22 '15 at 07:35

Thank you very much for your reply, i went the code that you have posted by what i really dont undertand is i don't see any file xl file path, i have to insert data in into the table every month, and the xlfile is saved in specific folder,given static Name, what i do is first i rewrite the field name on excel via vba to match with my table field name in access,where i activate the xlfile , after that i am trying to import. Since i am working on the access it self i dont think i have to give a string for database connection.I am really confused here It would be very great full if you could explain it more for me. Thank you Manoj

i need to copy and paste data from one sheet to another based on the value on the column. For eg: In my source sheet in column — user2561662, May 03 '19 at 00:23

Inserting excel sheet in Existing table in Access

1 Answers1