Excel Listview listitems to access instead of spreadsheets

Dear Experts,
I am writing simple code in excel's vba. you can see a sample from my file and I wrote some explain in vba userform. I am using userform and listview, listview is working good. but I want to use access database instead of excel sheets. Excel grown when I add more rows and it is only 65536 rows.
It will more comfortible and be more professional for me.

How can I take the data to my excell listview from Access
How can I save and change data in Access from listview
How can I delete in Access from listview

I remain with my best regards,
Tansu Erkanli
Clientele.xls
TansuErkanliAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

patrickabCommented:
TansuErkanli,

Is the data in your file meant to be private? If it is you should ask for it to be deleted as this is a public forum.

Patrick
0
TansuErkanliAuthor Commented:
Hello Patrick,
My little program is working in Excel VBA with userform. and writing to excel sheet and I can take the data to my ListView in userform.
For example I am writing to Excel sheet this code.
I want to learn how can I use ado file; also can I change the code for access mdb?

Private Sub CmdSave_Click()
' I want to use Access database instead of Excel sheet
' This is for new record
    Sheets("Clientele").Select
    Set Sh = ActiveSheet
    If NewRecord = True Then
        EndRow = Sh.Cells(65536, 1).End(xlUp).Row
        Sh.Cells(EndRow + 1, 1) = TextBox1.Text
        Sh.Cells(EndRow + 1, 2) = TextBox2.Text
    Else
' this is for edit the record
        y = ListView1.SelectedItem.index
        Pos = ListView1.ListItems(y).ListSubItems(3).Text
        Sh.Cells(Pos, 1) = TextBox1.Text
        Sh.Cells(Pos, 2) = TextBox2.Text
    End If
End Sub

Best regards,
Tansu
0
patrickabCommented:
Tansu,

Please confirm that the data in your file in NOT private.

Patrick
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

TansuErkanliAuthor Commented:
Dear Patrick,
The data is not private. This is only for example. I am last user. I am using office 2003.
To write program on Excel VBA is hobby for me also I am using them in my some job. I know  the code in VBA and excel, but I need help for export to Access database and read from Access.
I am adding my excel file and access database with last change,maybe you didn't see the Turkish characters.
I found some code from internet about this application.
* Create an Access table
* Write all data from Excel sheet to Access database, but it is writing again end of access.
Also I want to write from userform to access and userform read from access.
Best regards and thank you for your interest.
Tansu
Clientele.xls
Clientele.mdb
0
patrickabCommented:
Tansu,

I have asked others to help in this question as Access is not within my area of knowledge. Please give them time to respond. If you do not receive help please make a comment here so that I receive an automatic email.

Patrick
0
zorvek (Kevin Jones)ConsultantCommented:
The tutorial below will get you started.

The following text and sample code illustrates how to:

   -> open a database connection
   -> use a stored procedure to perform a query
   -> execute any SQL command against a database
   -> open a recordset using a custom query
   -> open a recordset using a table name
   -> check for an empty recordset
   -> read all records in a recordset
   -> add a record to a recordset
   -> delete a record from a recordset
   -> copy a recordset with headers to a worksheet
   -> close a recordset and database

This sample code, except for Open database method, can be used with any database such as Access, SQL Server, or Oracle. When using a database, most interaction happens via a recordset. Data is manipulated almost entirely using Recordset objects. Any number of Recordset objects can be created and used at the same time - each representing a different query or the same query. Different Recordset objects can access the same tables, queries, and fields without conflicting.

After opening a Recordset the Recordset can contain zero or more records. One record in the Recordset is always the current record except when the Recordset BOF or EOF property is true in which case no record is the current record. The current record is the record that is affected by any record-specific methods. To move amongst the records in a Recordset use the MoveNext, MovePrevious, MoveLast, and MoveFirst Recordset methods. A specific record can be made the current record by setting the AbsolutePosition property to the index number of the desired record. Fields in the current record are access as illustrated below.

   Value = MyRecordset!Field1
   MyRecordset!Field2 = Value + 1

When the current record is changed use the Update method to apply the changes to the database. Use the Add method to add a new record and the Delete method to delete the current record. The Add method can be used even if the query returns an empty recordset.

Caution about using the RecordCount method: The RecordCount for a serverside recordset may return a -1. This occurs with ActiveX Data Objects (ADO) version 2.0 or later when the CursorType is adOpenForwardonly or adOpenDynamic and when with ADO 1.5 only when the cursortype is adOpenForwardonly. To get around this problem use either adOpenKeyset or adOpenStatic as the CursorType for server side cursors or use a client side cursor. Client side cursors use only adOpenStatic for CursorTypes regardless of which CursorType is selected.

Before writing any ADODB code the data objects library "Microsoft ActiveX Data Objects x.x Library" must be referenced in the VBA project (Tools->References).

For additional information on the ADODB interface see the MSDN pages at:

   http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadoapireference.asp

Sample code:

   Dim MyDatabase As ADODB.Connection
   Dim MyCommand As ADODB.Command
   Dim MyRecordset As ADODB.RecordSet
   Dim Column As Long

   ' Open database connection
   Set MyDatabase = New ADODB.Connection
   MyDatabase.CursorLocation = adUseClient
   MyDatabase.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='C:\full\path\to\database.mdb'; User Id=admin; Password=;"
   ' For more information about Open syntax:
   '   http://msdn.microsoft.com/en-us/library/ms808201.aspx
   ' Additional help constructing connection strings can be found at http://www.connectionstrings.com/

   ' Query database using stored procedure (requires command object)
   Set MyCommand = New ADODB.Command
   Set MyCommand.ActiveConnection = MyDatabase
   MyCommand.CommandText = "qrySomeQuery" ' <- name of procedure
   MyCommand.CommandType = adCmdStoredProc
   With MyCommand
      .Parameters.Refresh
      .Parameters.Append .CreateParameter("QueryTextParam", adVarChar, adParamInput, 10, StringValue)
      .Parameters.Append .CreateParameter("QueryTextParam", adDouble, adParamInput, , DoubleValue)
      .Parameters.Append .CreateParameter("QueryLongParam", adBigInt, adParamInput, , LongValue)
      .Parameters.Append .CreateParameter("QueryDateParam", adDate, adParamInput, , DateValue)
      .Parameters.Append .CreateParameter("QueryDateTimeStampParam", adDBTimeStamp, adParamInput, , DateTimeValue)
      .Parameters.Append .CreateParameter("BooleanParam", adBoolean, adParamInput, , BooleanValue)
      ' For more information about CreateParameter syntax:
      '   http://msdn.microsoft.com/en-us/library/ms808298.aspx
   End With
   
   ' Open recordset using command object
   Set MyRecordset = New ADODB.Recordset
   MyRecordset.Open MyCommand, , adOpenDynamic, adLockPessimistic
   ' For more information about Open method syntax:
   '   http://msdn.microsoft.com/en-us/library/ms808656.aspx
   
   ' Build a custom query using command object
   Set MyCommand = New ADODB.Command
   With MyCommand
      Set .ActiveConnection = MyDatabase
      .CommandType = adCmdText
      .CommandText = "SELECT * From tblMyTable WHERE (tblMyTable.MyID = 1)"
   End With
   MyRecordSet.Open MyCommand, , adOpenDynamic, adLockReadOnly

   ' Execute any SQL statement
   MyDatabase.Execute "INSERT INTO TableName (Field1, Field2) VALUES ('" & Range("A1").Value & "','" & Range("A2").Value & "')"

   ' Open a recordset by specifying specific table (no query)
   MyRecordset.Open "TableName", MyDatabase, adOpenDynamic, adLockPessimistic
   
   ' Open a recordset by specifying query without using command object
   MyRecordset.Open "SELECT * FROM MyTable", MyDatabase, adOpenDynamic, adLockPessimistic

   ' Test for no records
   If MyRecordset.BOF And MyRecordset.EOF Then
      MsgBox "No records in table"
   End If

   ' Determine total records (see notes above about inconsistencies with the RecordCount method)
   MsgBox "Total records: " & MyRecordset.RecordCount
   
   ' Look at all records in record set
   While Not MyRecordset.EOF
      MsgBox "Record number: " & MyRecordset.AbsolutePosition
      MyRecordset.MoveNext
   Wend

   ' Find a specific record given a field value
   MyRecordset.MoveFirst
   MyRecordset.Find "ID='ABC123'"
   If Not MyRecordset.BOF And Not MyRecordset.EOF Then
      MyRecordset!Field1 = "Match Found"
      MyRecordset.Update
   End If

   ' Copy the entire recordset to a worksheet (this technique does not copy field names)
   Sheets("Sheet1").[A2].CopyFromRecordset MyRecordset
   
   ' Create headers and copy data
   With Sheets("Sheet1")
      For Column = 0 To MyRecordset.Fields.Count - 1
         .Cells(1, Column + 1).Value = MyRecordset.Fields(Column).Name
      Next
      .Range(.Cells(1, 1), .Cells(1, MyRecordset.Fields.Count)).Font.Bold = True
     .Cells(2, 1).CopyFromRecordset MyRecordset
   End With

   ' Update current record
   MyRecordset!Field1 = "Some data"
   MyRecordset!Field2 = "Some more data"
   MyRecordset.Update

   ' Move specific fields from current record to worksheet
   With Sheets("Sheet1")
      Cells(Row, "A") = MyRecordset!Field1
      Cells(Row, "B") = MyRecordset!Field2
   End With
   
   ' Add new record and set field values
   MyRecordset.AddNew
   MyRecordset!Field1 = "Some data"
   MyRecordset.Update

   ' Update an existing record or add it if it does not exist
   MyRecordset.MoveFirst
   MyRecordset.Find "Field1='"& SourceSheet.Cells(Row, "A") & "'"
   If MyRecordset.BOF Or MyRecordset.EOF Then
      MyRecordset.AddNew
   End If
   MyRecordset!Field1 = SourceSheet.Cells(Row, "A")
   MyRecordset!Field2 = SourceSheet.Cells(Row, "B")
   MyRecordset.Update
   
   ' Delete current record
   MyRecordset.Delete

   ' Close recordset
   MyRecordset.Close

   ' Close database
   MyDatabase.Close

Kevin
0
TansuErkanliAuthor Commented:
Dear Kevin,
First of all many thanks for your reply. I used the cod to my userform and working. I can import my sheet from access now. I research how can I take my ListView the data without to record sheet.
I know excel userform, listview and others but ADO is new for me. By the way I didn't find any example workbook for ADO. I think that it is diffucult for me.

Best regards,
Tansu

Private Sub Access_Data_Click()
    Set MyDatabase = New ADODB.Connection
    MyDatabase.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
                    "Data Source=" & ThisWorkbook.Path & "\Clientele.mdb"
    sSQL = "SELECT ClienteleCod, ClienteleName, Address,Contact, Assistant, Phone, PhoneDirect,  " & _
           "Fax, Cellphone, Tax, Banc, EMail , Notes, Picture FROM Clientele;"

    'Create RecordSet
    With MyDatabase
        Set MyRecordset = .Execute(sSQL)
    End With

    ' Create headers and copy data
    With Sheets("Sayfa1")
        For Column = 0 To MyRecordset.Fields.count - 1
            .Cells(1, Column + 1).Value = MyRecordset.Fields(Column).Name
        Next
        .Range(.Cells(1, 1), .Cells(1, MyRecordset.Fields.count)).Font.Bold = True
        .Cells(2, 1).CopyFromRecordset MyRecordset
    End With

    ' Close recordset
    MyRecordset.Close
    ' Close database
    MyDatabase.Close

End Sub
0
zorvek (Kevin Jones)ConsultantCommented:
What is your question at this point?

Kevin
0
TansuErkanliAuthor Commented:
Dear Kevin,

I want to import my access data to excel Listview instead of sheet.
First  time, I import to sheet from access and ListView taking data from sheet. When the data long, it is taking many times.

Tansu
0
TansuErkanliAuthor Commented:
Dear Kevin,
I tried this cod and coming to ListView now. I am writing here for help to other user like problem me.

    ListView1.ListItems.Clear
    s = 1
    Do While Not MyRecordset.EOF
        With ListView1
            .ListItems.Add , , MyRecordset(0)
            .ListItems(s).ListSubItems.Add , , MyRecordset(1)
            .ListItems(s).ListSubItems.Add , , MyRecordset(2)
            .ListItems(s).ListSubItems.Add , , MyRecordset(3)
            .ListItems(s).ListSubItems.Add , , MyRecordset(4)
            .ListItems(s).ListSubItems.Add , , MyRecordset(5)
            .ListItems(s).ListSubItems.Add , , MyRecordset(6)
            .ListItems(s).ListSubItems.Add , , MyRecordset(7)
            .ListItems(s).ListSubItems.Add , , MyRecordset(8)
            .ListItems(s).ListSubItems.Add , , MyRecordset(9)
            .ListItems(s).ListSubItems.Add , , MyRecordset(10)
            .ListItems(s).ListSubItems.Add , , MyRecordset(11)
            .ListItems(s).ListSubItems.Add , , MyRecordset(12)
            s = s + 1
        End With
        MyRecordset.MoveNext
    Loop

0
zorvek (Kevin Jones)ConsultantCommented:
Excellent!

Kevin
0
TansuErkanliAuthor Commented:
Hello Dear Experts,
I have a problem about this code. Code is too long and going to rows end, How can I enter second row.
Like this syntax " & _

sSQL = "SELECT ClientCod, ClientName, Address, ContactPerson, Assistant, Phone, PhoneDirect, Fax, CellPhone, TaxNo, BancNo, EMail, Notes, PicturePath, Debt, Credit, DebtBalance, CreditBalance FROM Clientele;"
Best Regards
Tansu Erkanli
0
zorvek (Kevin Jones)ConsultantCommented:
sSQL = "SELECT ClientCod, ClientName, Address, ContactPerson, " _
& "Assistant, Phone, PhoneDirect, Fax, CellPhone, TaxNo, BancNo, " _
& "EMail, Notes, PicturePath, Debt, Credit, DebtBalance, CreditBalance FROM Clientele;"

Kevin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TansuErkanliAuthor Commented:
Dear Kevin,
it is working, you are the best, you are the top.
Thanks,
Tansu

For information the other member. maybe they can use the code.
Only I need change data code. I am working!

I wrote this code for delete record and it is working.
Private Sub CmdDelete_Click()
    Dim MyRecordset As New ADODB.Recordset, s As Integer
    MyRecordset.Open "select*from Clientele ", myConnection, adOpenDynamic, adLockOptimistic
        myConnection.Execute " delete from Clientele where ClientCod = '" & TextBox1 & "'"
        CmdSort_Click ' sort again the ListView1
        Set MyRecordset = Nothing
    End If
End Sub

I wrote this code for find record, it is working.
Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    ListView1.ListItems.Clear
    Dim MyRecordset As New ADODB.Recordset, s As Integer, FindRecord As String
    If TextBox2.Text = "" Then: CmdSort_Click: Exit Sub
    FindRecord = "select * FROM [Clientele] where ClientName LIKE '%" & TextBox2.Text & "%'"
    MyRecordset.Open FindRecord, myConnection, 3, 1
    s = 1
    Do While Not MyRecordset.EOF
        With ListView1
            .ListItems.Add , , MyRecordset(0)
            .ListItems(s).ListSubItems.Add , , MyRecordset(1)
            .ListItems(s).ListSubItems.Add , , MyRecordset(2)
            s = s + 1
        End With
        MyRecordset.MoveNext
    Loop
    Set MyRecordset = Nothing
    FindRecord = Empty
    s = Empty
End Sub
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.