Store in DataBase

[ksilenio]

Hi to everyone
I'd like to ask how can i will stored some data in a database? How can i doit this?

[Logit]

Option Explicit
Private Sub btnCancel_Click()
    Unload Me
End Sub


Private Sub btnOK_Click()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim newRow As Long
    
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
    
    'The next two lines can be expanded as many times as needed for all the entry fields in your project
    
    ws.Cells(newRow, 1).Value = Me.txtFirstName.Value
    ws.Cells(newRow, 2).Value = Me.txtSurname.Value
    
End Sub
Sub CommandButton1_Click()
    Selection.EntireRow.Delete
End Sub

Attached find the workbook. This is the basic setup for creating a database. You can add as many TextBox fields as required and amend the code as required.

[ksilenio]

Hi Logit .Thank you for your answer but i don't understant , where is the name of the DB and the fields, and how to recall the entries?

[Bob Phillips]

Your question needs a lot more detail.

What do you mean by a database? SQL Server, Access, Excel workbook, text file?

What do you mean by store data? Insert new data, update existing data, delete data?

Where will the data originate from? Will it be a text file, will users input to an Excel worksheet, will they input to a userform?

How will you control the application?

Etc. Etc. Etc.

and what can you do yourself?

[ksilenio]

Hi xld .To answer your questions
1. SQL Server . Where i will find this. Is it with Excell? . How to connect it?
2. Insert,update,delete ,....
3. I will manage all these from a userform
4. The app isn't an Excell but a SolidWorks app , but they have the same VBA language
Regards

[Logit]

What level of experience do you presently have creating / editing / manipulating data into a "database" ?

Your current level of understanding would be an indicator of what material to supply to you for review. Also,
please provide answers to xld's posting as those answers would also provide guidance how to respond to your request.

Thanks.

[ksilenio]

I used to programming in DELPHI with SQL DataBase so my experience to a DB is good enough(for the DELPHI). Now i am furniture designer and i use the SolidWorks for that . I am creating some macros in VBA language (i am new in VBA language) so i have some blanks in VBA , how to connenct with the SQL server , how to manipulate the data etc

[Logit]

.
Great ! There are volunteers here and other forums that should be able to assist you with your needs. Regretfully, I do not know anything about SQL.

Best wishes.

[paulked]

As this is an Excel forum, most people here will not be familiar with SolidWorks therefore, if I were you, I'd post this question in a more suitable forum.

[Bob Phillips]

SQL is fine, loads of us here know it. What we might not know is SolidWorks, how accessible it is, what the connection strings are, what the table structures are.

But the question you ask is big, so I think we need to take it in small steps. We cannot possibly build the whole app from what you have given us, so best to post several different questions on different aspects.

What is the first ting you want to do in this app (keep it manageable)?

[ksilenio]

Hi to all .Thank you all for your answers ,but i asked how can i connect from vba environment to a SQL Database , if its possible.
Anyway if its too difficult i will pick another way

[paulked]

Sub WriteToDB
    Dim cn As New ADODB.Connection
    #If Win64 Then
    cn.Open "Provider=MSDASQL;DRIVER={mysql odbc 5.2 unicode driver}" _
    & ";SERVER=localhost" _
    & ";PORT=3306" _
    & ";DATABASE=SomeDBase" _
    & ";UID=UserName" _
    & ";PWD=Password"
    #Else
    cn.Open "DRIVER={MySQL ODBC 3.51 Driver}" _
    & ";SERVER=localhost" _
    & ";PORT=3306" _
    & ";DATABASE=SomeDBase" _
    & ";UID=UserName" _
    & ";PWD=Password"
    #End If
    cn.Execute "UPDATE ked_festival_ticket_holders_attendance_" & DATA.Cells(13, 1) & " SET " & Evnt & " = '" & yn & "' WHERE Ticket_No = " & Tkt
    cn.Close
    Set cn = Nothing
End Sub

This checks if you are using 64 or 32bit Excel then connects to an SQL database.

[ksilenio]

Hi I copied the code above but i have a compile error " User-defined type not defined" on "Dim cn As New ADODB.Connection" .

[paulked]

You probably haven't set the Active X reference in the VBE window.

[ksilenio]

You mean "Microsoft ActiveX... I have a lot of these .Look scr(56)

[paulked]

Which ones have you tried? I would think the latest one. If you want to work with recordsets too then you may need that reference as well.

I suggest you do some Googling, there are numerous posts on connections and connection strings.

[ksilenio]

I have checked the latest but i have some issues . Propably i must install the SQLSERVER 2014 . I don't know but i have all the package on my PC but it's need to install it

[paulked]

Have you got an SQL database to test with?

[ksilenio]

No i haven't . I am looking for the installation but i can't find any. Propably i don't have the SQLServer . Anyway i will looking for that and i will try again .Anyway Thank you for all

[paulked]

Before you do that, how many records do you want to store? 100's, 1,000's or 10,000+ records? You could do 1,000's in Excel without a problem.