Solved

DAO connection to SQLEXPRESS, add new record and update

Posted on 2008-10-12
24
640 Views
Last Modified: 2012-05-05
Hello,

I have been working for a long time now to find a solution for using vb6 together with DAO to
1. Create new SQLserver database
2. Create new tables
3. Perform Queries
4. Update tables.

I have searched other posts as well, but so far I did not find a solution. would somebody be so kind to give me a example code for this?

I am offering 500 points to walk me through this process.

Regards,
Dennis
0
Comment
Question by:Schuttend
  • 13
  • 11
24 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22698625
why DAO? ADO is more appropriate...

dim ado as adodb.connection
set ado = new adodb.connection
' for connection strings to sql express, see: http://www.connectionstrings.com/?carrier=sqlserver2005
ado.open "connection string goes here" 
'create a database (means your connection should specify "master" as database initially
'the example shown is the minimum code, leaving all the options at default.
'check the CREATE DATABASE docs/syntax for more information
ado.execute "CREATE DATABASE [name_of_database]" 
'create a table:
ado.execute "CREATE TABLE [name_of_database].dbo.[name_of_table] (col1 int identity primary key, data varchar(10), date_col datetime) " 

'perform a query:
dim cmd as adodb.command
dim rst as adodb.recordset 
set cmd = new adodb.command
set cmd.activeconnection = ado
cmd.commandtext = "SELECT * FROM [name_of_database].dbo.[name_of_table] "
set rst = cmd.execute 
while not rst.eof
  
  rst.movenext
wend
rst.close
set rst = nothing
set cmd = nothing 
'etc ...
ado.close
set ado = nothing

Open in new window

0
 

Author Comment

by:Schuttend
ID: 22756811
Hi,

First problem from this code arises from selecting the correct component, because I get an error message in line : dim ado as adodb.connection.
Which component should I add??

Regards,
Dennis
ado.jpg
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22756845
Microsoft ActiveX Data Objects 2.x  (with x being the highest possible, ie 2.8 for example)
0
 

Author Comment

by:Schuttend
ID: 22757731
I found the correct object reference and I also got the connectionstring. Still I get attached error.
Any ideas??

Regards,
Dennis
errorado.jpg
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22758228
0
 

Author Comment

by:Schuttend
ID: 22759062
Ok next step... with connectionstring:
ado.Open "Driver={SQL  Native Client};Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"

Is giving me a login error. Any suggestions?

Regards,
Dennis
Login.jpg
loginsa.jpg
Management.jpg
Security.jpg
0
 

Author Comment

by:Schuttend
ID: 22759751
Please disregard last message. Connection works now...
I am only missing the code for adding data to a table.
Could somebody tell me this?

Regards,
Dennis
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22760528
again, the adodb.command object is the easiest way to go:
dim cmd as adodb.command

set cmd = new adodb.command

set cmd.activeconnection = ado

cmd.commandtext = "INSERT INTO [name_of_table] (col1, col2) VALUES (@val1, @val2) "

cmd.parameters.Append cmd.CreateParameter("@val1", adParamInput, adVarchar, 30, 'test') 

cmd.parameters.Append cmd.CreateParameter("@val1", adParamInput, adVarchar, 50, 'value') 

cmd.execute 

Open in new window

0
 

Author Comment

by:Schuttend
ID: 22775403
Hi,

I get an error in the code. Both in the cmd.parameters.append lines. Do you have a solution for this?

regards,
Dennis
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22775451
>I get an error in the code.
it might be helpful to know what error you get???

anyhow, it looks like I reversed 2 parameters
dim cmd as adodb.command

set cmd = new adodb.command

set cmd.activeconnection = ado

cmd.commandtext = "INSERT INTO [name_of_table] (col1, col2) VALUES (@val1, @val2) "

cmd.parameters.Append cmd.CreateParameter("@val1", adVarchar, adParamInput, 30, 'test') 

cmd.parameters.Append cmd.CreateParameter("@val1", adVarchar, adParamInput, 50, 'value') 

cmd.execute 

Open in new window

0
 

Author Comment

by:Schuttend
ID: 22775641
As you can see, this is not the solution. Or do I need some additional components to use this code?

Regards,
Dennis
errorcmd.jpg
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22775721
well, i wrote 'test', and it should be "test" ... it's the value you want to pass as argument for the parameter object...
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Schuttend
ID: 22775816
Hi,

Thanks. I am splitting up the code for learning purposes.Please see code snippet which gives an error.
Also the 'errorpic' is attached about column name being incorrect.
Into the command text line it's not clear to me what the difference is between the first val1 and the second one.
INSERT INTO [Table1] (val1,val2) VALUEs (val1,val2) " 

'Create Table

Private Sub Command3_Click()

Dim ado As ADODB.Connection

Set ado = New ADODB.Connection

Dim name As String

name = "woensdag"
 

ado.Open "Driver={SQL Server Native Client 10.0};Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"

ado.Execute "CREATE TABLE [monday].dbo.[Table1] (col1 int identity primary key, val1 varchar(10), val2 varchar(10)) "

ado.Close

Set ado = Nothing
 

End Sub

'Add Data to Table

Private Sub Command4_Click()

Dim ado As ADODB.Connection

Set ado = New ADODB.Connection
 

ado.Open "Driver={SQL Server Native Client 10.0};Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"
 

Dim cmd As ADODB.Command

Set cmd = New ADODB.Command

Set cmd.activeconnection = ado
 
 

cmd.commandtext = "INSERT INTO [Table1] (val1, val2) VALUES (val1, val2) "

cmd.Parameters.Append cmd.CreateParameter("val1", adVarChar, adParamInput, 30, "test")

cmd.Parameters.Append cmd.CreateParameter("val2", adVarChar, adParamInput, 50, "Value")

cmd.Execute

ado.Close

Open in new window

erroradd.jpg
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22775854
>cmd.commandtext = "INSERT INTO [Table1] (val1, val2) VALUES (val1, val2) "
is wrong, it must be:

cmd.commandtext = "INSERT INTO [Table1] (val1, val2) VALUES (@val1, @val2) "

otherwise, the parameters will not be placed correctly

0
 

Author Comment

by:Schuttend
ID: 22775936
Hi,

Just that I understand correctly. Strings "Test" is placed in column1 (val1) and"Value" is placed in column2 (val2).
however I get a error about the @val1 being not declared, please see attached pic.

'Add Data to Table

Private Sub Command4_Click()

Dim ado As ADODB.Connection

Set ado = New ADODB.Connection
 

ado.Open "Driver={SQL Server Native Client 10.0};Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"
 

Dim cmd As ADODB.Command

Set cmd = New ADODB.Command

Set cmd.activeconnection = ado
 
 

cmd.commandtext = "INSERT INTO [Table1] (col1, col2) VALUES (@val1, @val2) "

cmd.Parameters.Append cmd.CreateParameter("@val1", adVarChar, adParamInput, 30, "test")

cmd.Parameters.Append cmd.CreateParameter("@val2", adVarChar, adParamInput, 50, "Value")

cmd.Execute

ado.Close
 
 

End Sub

Open in new window

errorval.jpg
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22776004
interesting...

'Add Data to Table

Private Sub Command4_Click()

Dim ado As ADODB.Connection

Set ado = New ADODB.Connection

 

ado.Open "Driver={SQL Server Native Client 10.0};Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"

 

Dim cmd As ADODB.Command

Set cmd = New ADODB.Command

Set cmd.activeconnection = ado

 

 

cmd.commandtext = "INSERT INTO [Table1] (col1, col2) VALUES (@val1, @val2) "

cmd.NamedParameters = true

cmd.Parameters.Append cmd.CreateParameter("@val1", adVarChar, adParamInput, 30, "test")

cmd.Parameters.Append cmd.CreateParameter("@val2", adVarChar, adParamInput, 50, "Value")

cmd.Execute

ado.Close

 

 

End Sub

Open in new window

0
 

Author Comment

by:Schuttend
ID: 22776167
Interesting?? What do you mean by that?

Regards,
Dennis
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22776190
I mean that normally, it should work without the line I added (cmd.NamedParameters = true)
0
 

Author Comment

by:Schuttend
ID: 22776283
unfortunetely still the same error....
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22776432
could you please try this:
ado.Open "Provider=SQLNCLI;Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"

Open in new window

0
 

Author Comment

by:Schuttend
ID: 22776627
Does not work. Provider cannot be found. I am using SQLServer 2008 express....


provider.jpg
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22776695
:-(
0
 

Author Comment

by:Schuttend
ID: 22777429
With the code I can now
1. Create database
2. Delete database
3. Create a table

The only thing I am missing is to add data into the table...
'Create Database
 

Private Sub Command1_Click()

Dim ado As ADODB.Connection

Set ado = New ADODB.Connection

' for connection strings to sql express, see: http://www.connectionstrings.com/?carrier=sqlserver2005

ado.Open "Driver={SQL Server Native Client 10.0};Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"

'ado.Open "Driver={SQL Native Client};Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"

'create a database (means your connection should specify "master" as database initially

'the example shown is the minimum code, leaving all the options at default.

'check the CREATE DATABASE docs/syntax for more information

ado.Execute "CREATE DATABASE [monday]"

'create a table:

'ado.Execute "CREATE TABLE [monday].dbo.[Table1] (col1 int identity primary key, data varchar(10), date_col datetime) "
 

'perform a query:

'Dim cmd As ADODB.Command

'Dim rst As ADODB.Recordset

'Set cmd = New ADODB.Command

'Set cmd.activeconnection = ado

'cmd.commandtext = "SELECT * FROM [monday].dbo.[Table1] "

'Set rst = cmd.Execute

'While Not rst.EOF

  

 ' rst.movenext

'Wend

'rst.Close

'Set rst = Nothing

'Set cmd = Nothing

'etc ...

ado.Close

Set ado = Nothing

End Sub

' Delete Database

Private Sub Command2_Click()

Dim ado As ADODB.Connection

Set ado = New ADODB.Connection

ado.Open "Driver={SQL Server Native Client 10.0};Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"

ado.Execute "DROP DATABASE [monday]"

ado.Close

Set ado = Nothing
 
 

End Sub

'Create Table

Private Sub Command3_Click()

Dim ado As ADODB.Connection

Set ado = New ADODB.Connection

Dim name As String

name = "woensdag"
 

ado.Open "Driver={SQL Server Native Client 10.0};Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"

ado.Execute "CREATE TABLE [monday].dbo.[Table1] (col1 int identity primary key, val1 varchar(10), val2 varchar(10)) "

ado.Close

Set ado = Nothing
 

End Sub

'Add Data to Table

Private Sub Command4_Click()

Dim ado As ADODB.Connection

Set ado = New ADODB.Connection

 

ado.Open "Driver={SQL Server Native Client 10.0};Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"

'ado.Open "Provider=SQLNCLI;Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"

Dim cmd As ADODB.Command

Set cmd = New ADODB.Command

Set cmd.activeconnection = ado

 

 

cmd.commandtext = "INSERT INTO [Table1] (col1, col2) VALUES (@val1, @val2) "

cmd.NamedParameters = True

cmd.Parameters.Append cmd.CreateParameter("@val1", adVarChar, adParamInput, 30, "test")

cmd.Parameters.Append cmd.CreateParameter("@val2", adVarChar, adParamInput, 50, "Value")

cmd.Execute

ado.Close

 

 

End Sub

Open in new window

0
 

Author Closing Comment

by:Schuttend
ID: 31505485
thanks again
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Opening Remote & Local Data Connection 2 51
Access 2016 VB code 9 102
using web browser with BING 40 110
Excel Automation VBA 19 25
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now