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 (http://www.vbaexpress.com/forum/member.php?2139-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.

[ksilenio]

For the moment i don't know ,but are there any problem with the numbers of the records?

[paulked]

Only look-up speed when you have thousands of them. I've used around 8,000 on one sheet and Excel had no problem with that.

[Bob Phillips]

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

But you should never auto-instantiate objects.

[Bob Phillips]

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.

It is important to know what you are connecting too so that the correct connection string is used. You said that you were using Solidworks, but you seem to be confusing that and SQL Server.

[paulked]

Hi xld


But you should never auto-instantiate objects.

I appreciate that when you instantiate an object it takes up memory, but didn't realise that selecting a reference had any impact on memory until the object was used.

[SamT]

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

You must learn the coding starting from the SolidWorks data base you use.
What is the SQL language (Code) the SolidWorks DB engine understands? Oracle SQL: MariaDB SQl, PostGreSQL SQL, FileMaker SQL, Or even Solidworks SQL

Then learn the Language (code) the DB connection Device/System uses. ADO.NET, ADO, JET, DAO, Or even SolidADO.

VBA can transmit any language (SQL code words) needed to and thru any Connection device:



When paulked and XLD are talking about "References" they are talking about Early Binding vs Late Binding. Early binding is slightly simpler and works well for the Standard (always present: Application, Office, and VBA) References, but Late Binding is much more robust and universal.

Google "VBA Early Late Binding" for more information.

[Bob Phillips]

I appreciate that when you instantiate an object it takes up memory, but didn't realise that selecting a reference had any impact on memory until the object was used.

Does your response have any relevance to what I said? My comment was about auto-instantiating, not instantiating. It is a bad practice that can have unexpected results.

[Bob Phillips]

You must learn the coding starting from the SolidWorks data base you use.
What is the SQL language (Code) the SolidWorks DB engine understands? Oracle SQL: MariaDB SQl, PostGreSQL SQL, FileMaker SQL, Or even Solidworks SQL

If he really is/wants to use Solidworks, I think he has a problem. Solidworks seems to be locked down, you cannot query it directly. They do provide an API that you can program, but that means learning a whole new box of tricks (not for me).

And to be perfectly frank, the OP is asking for an application, not just some code to access a database. I envisage a big job for someone.

[paulked]

Does your response have any relevance to what I said?

Not really:mkay, I didn't understand it fully before I read up on it :thumb

Off to party now... Happy New Year all!

[SamT]

I envisage a big job for someone.
Yea, the OP. Not me, I don't need SolidWorks. That's why I gave him clues as to the path he must follow