Excel transfer to SQL using VB

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • scoopthis
    New Member
    • Jun 2006
    • 1

    Excel transfer to SQL using VB

    Hi,
    I have an application where I transfer an excel file to a table on SQL through an adp file. The excel file does change so I pass the name parameter from a cmd line. The excel file has a specific range defined of 20,000 records. Currently the process is using ac.DoCmd.Transf erSpreadsheet acImport and it pulls in all 30,000 records even though there isn't data in all 20,000 records (lots of nulls). How can I get the process to only transfer the data where it isn't null?

    Thanks.
  • sashi
    Recognized Expert Top Contributor
    • Jun 2006
    • 1749

    #2
    hi there,

    wll i feel the "null" problem is taken care of when it comes to comma delimited .txt, .csv format file.. give it a try.. good luck my fren..

    Comment

    • CaptainD
      New Member
      • Mar 2006
      • 135

      #3
      VB can use the NZ() function, have you looked into that?

      Comment

      • Big K Hutch
        New Member
        • Jul 2006
        • 11

        #4
        Where do you place this in the code?

        Comment

        • jojeh_computer
          New Member
          • Jul 2006
          • 1

          #5
          Hello ,
          I have an application in vb that must gets excell applicatiom and transfer data from excell worksheet to table of sql? how can i do this work? tanks for reply.

          Comment

          • mysql
            New Member
            • Aug 2006
            • 1

            #6
            hi how can work with vb and mysql?
            pls reply early

            Comment

            • Roytop
              New Member
              • Sep 2006
              • 2

              #7
              Hi!
              Use this little VBA code for uploading Excel data to MS SQL database.

              Sub upload()
              Set connAS400 = CreateObject("A DODB.Connection ")
              connAS400.Open (" Provider=SQLOLE DB.1;InitialCat alog=dbname;Ser ver=servername; UID=user;PWD=pa ssword; ")
              ConnOpenAS400 = True
              connAS400.Comma ndTimeout = 100

              For i = 1 To Workbooks(1).Wo rksheets(1).Use dRows.Count
              query = "INSERT Roytop..test (text) VALUES ('" & Workbooks(1).Wo rksheets(1).Ran ge("A" & i) & "')"
              Set result = connAS400.Execu te(query)
              Next

              result.Close
              Set result = Nothing
              End Sub

              Comment

              • Phrackers
                New Member
                • Sep 2006
                • 1

                #8
                Originally posted by mysql
                hi how can work with vb and mysql?
                pls reply early
                simply.. just install the mysql database and mysql odbc driver.. then in visual basic just call the odbc driver to connect into the mysql database..in then..u have now to code the basic query of sql.. thanks..

                Comment

                • xpcer
                  New Member
                  • Jul 2006
                  • 51

                  #9
                  Originally posted by Phrackers
                  simply.. just install the mysql database and mysql odbc driver.. then in visual basic just call the odbc driver to connect into the mysql database..in then..u have now to code the basic query of sql.. thanks..
                  can i have the codes? thanks before

                  Comment

                  • xpcer
                    New Member
                    • Jul 2006
                    • 51

                    #10
                    Originally posted by Roytop
                    Hi!
                    Use this little VBA code for uploading Excel data to MS SQL database.

                    Sub upload()
                    Set connAS400 = CreateObject("A DODB.Connection ")
                    connAS400.Open (" Provider=SQLOLE DB.1;InitialCat alog=dbname;Ser ver=servername; UID=user;PWD=pa ssword; ")
                    ConnOpenAS400 = True
                    connAS400.Comma ndTimeout = 100

                    For i = 1 To Workbooks(1).Wo rksheets(1).Use dRows.Count
                    query = "INSERT Roytop..test (text) VALUES ('" & Workbooks(1).Wo rksheets(1).Ran ge("A" & i) & "')"
                    Set result = connAS400.Execu te(query)
                    Next

                    result.Close
                    Set result = Nothing
                    End Sub

                    For i = 1 To Workbooks(1).Wo rksheets(1).Use dRows.Count
                    query = "INSERT Roytop..test (text) VALUES ('" & Workbooks(1).Wo rksheets(1).Ran ge("A" & i) & "')"
                    Set result = connAS400.Execu te(query)
                    Next

                    cannot execute....

                    Comment

                    • CaptainD
                      New Member
                      • Mar 2006
                      • 135

                      #11
                      Originally posted by xpcer
                      For i = 1 To Workbooks(1).Wo rksheets(1).Use dRows.Count
                      query = "INSERT Roytop..test (text) VALUES ('" & Workbooks(1).Wo rksheets(1).Ran ge("A" & i) & "')"
                      Set result = connAS400.Execu te(query)
                      Next

                      cannot execute....
                      Did you change the values he gave you to match your configurations?

                      Comment

                      Working...