Solved

Recordset

Posted on 2004-08-22
32
1,894 Views
Last Modified: 2011-08-18
Hi every Body
I am trying to make a Program, I am using SQL Server as my Data Base and I am Using Microsoft Access in order to make my Forms and Reports.
Here is the question:
I want to use DAO Recordset in order to Enter some Data from a Form to a Table which is not related to my Form. I know how to this in Access, But Apparently it is a little different when I am using SQL Server. This is how I will do it in Access:
In VB Editor I go to Tools>References> and Chose the Microsoft DAO 3.6 Object Library.
Then I have created a command button on the Installer Form to Save the new record and then copy that information over to a History table with the InstallerID and InstallerName Fields already created.  Is this correct?  

Private Sub Save_Installer_Click()
On Error GoTo Err_Save_Installer_Click

Dim r2 As DAO.Recordset

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
   
   
    Set r2 = CurrentDb.OpenRecordsets("History")
    r2.AddNew
    r2("InstallerID") = Me.InstallerID
    r2("InstallerName") = Me.InstallerName
   
    r2.Update
    r2.Close

Exit_Save_Installer_Click:
    Exit Sub

Err_Save_Installer_Click:
    MsgBox Err.Description
    Resume Exit_Save_Installer_Click
It works in Access, But It Does not If I am using SQL Server as my Data Base, Could you tell me why?
I think it has to do of choosing the right Option in Tools>References.

Best Regards
Bijan

0
Comment
Question by:Bijanbijan
  • 16
  • 15
32 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11865413
You're right that you shouldn't be using DAO. You should be using ADO instead. ADO is a later protocol, and better in several ways.

You have to establish a connection to SQL Server, which is covered here:
http://www.experts-exchange.com/Databases/MS_Access/Q_20295538.html?query=ado+connection&topics=39

Then you need to execute an INSERT query to post the data to SQL Server, which is covered here:
http://www.experts-exchange.com/Databases/MS_Access/Q_20892053.html?query=insert+to+SQL+Server&topics=39
0
 
LVL 10

Expert Comment

by:avidya
ID: 11865420
Hi,

you have to use a differnt connection string to connect to the sql server:

<%
Set demoConn = Server.CreateObject("ADODB.Connection")
demoPath="DRIVER={SQLServer};" & _
"SERVER=TheServer;UID=TheUser;" & _
"PWD=ThePassword;DATABASE=TheDatabase"
demoConn.open demoPath
%>

Be SURE you replace "TheServer" with the name of the server, "TheUser" with your user name, "ThePassword" with the database password, and TheDatabase with the name of the database.
0
 

Author Comment

by:Bijanbijan
ID: 11867695
Dear avidya
Thanks a Lot for your reply.
But It is not working for me at the moment I made the following Code, from your suggestion:

Private Sub Command75_Click()
Set demoConn = Server.CreateObject("ADODB.Connection")
demoPath = "DRIVER={SQLServer};" & _
"SERVER=Bijan;UID=sa;" & _
"PWD=ba;DATABASE=Northwind"
demoConn.Open demoPath

End Sub

But I get the Following Error: (Compile error Variable Not Defined) and it gets Focused on Second Line (Set demoConn = Server.CreateObject("ADODB.Connection") )
I noticed that in above coding there is no mention of Destenation Table and also the fields that the Data must come from and go to therefore I added the following codes:
Private Sub Command75_Click()
Set demoConn = Server.CreateObject("ADODB.Connection")
demoPath = "DRIVER={SQLServer};" & _
"SERVER=Bijan;UID=sa;" & _
"PWD=ba;DATABASE=Northwind"
demoConn.Open demoPath
Set r2 = CurrentDb.OpenRecordset("Table1")
    r2.AddNew
    r2("OrderID") = Me.OrderID
    r2("QoteID") = Me.QoteID
   
    r2.Update
    r2.Close

End Sub
From my DAO experience, But it still is not working.

Could you tell in Detail what is it that I am nod doing correctly.
I should mention that I am writing the above codes “as you might know” on The On_Click event of a Button (Command75) in VB Editor. Is this right?

Best regards
Bijan Bijan



0
 

Author Comment

by:Bijanbijan
ID: 11867703
Dear jdlambert1
Thanks a Lot for your reply.
But It is not working for me at the moment I made the following Code, from your suggestion:

Private Sub Command76_Click()
CurrentDb.Execute "INSERT into dbo_Table1  (OrderID, QoteID) values(int5, int5)"
End Sub
 But I get this Message or Error ( You can not go to specified record)

Then I tried the fOllowing Codes:

Private Sub Command76_Click()
Dim myCn As New ADODB.Connection
Dim myRs As New ADODB.Recordset
   
myCn.ConnectionString = "DSN=Northwind"
myCn.Open
myRs.Open "SELECT * FROM Table1", myCn, adOpenDynamic


CurrentDb.Execute "INSERT into Table1  (OrderID, QoteID) values(OrderID, QoteID)"

End Sub

Ans I still get the Debug Error: (Run time error 91 Object variable or with Block variable Not set.)
I should mention that I am writing the above codes “as you might know” on The On_Click event of a Button (Command76) in VB Editor. Is this right?

Please write to me the Complete Coding, Since I am new in SQL.

Best regards
BijanBijan
0
 
LVL 10

Expert Comment

by:avidya
ID: 11868990
Hi,

You got to dim the vars in vb before you can use them, should have told you that, sorry.
Try this:

Private Sub Command75_Click()
  Dim demoPath As String
  Dim demoConn As ADODB.Connection
  Dim r2 As ADODB.Recordset

 Set demoConn = Server.CreateObject("ADODB.Connection")
 demoPath = "DRIVER={SQLServer};" & "SERVER=Bijan;UID=sa;" & "PWD=ba;DATABASE=Northwind"
demoConn.Open demoPath
Set r2 = CurrentDb.OpenRecordset("Table1")
    r2.AddNew
    r2("OrderID") = Me.OrderID
    r2("QoteID") = Me.QoteID
   
    r2.Update
    r2.Close

End Sub
0
 

Author Comment

by:Bijanbijan
ID: 11871477
Hi Avidya
Thanks so much for your quick reply.
I copied the Exact coding in the Click Event of my Command75 Button,
But I stil get exact smae error as Before:
(Compile error Variable Not Defined) and it gets Focused on Second Line (Set demoConn = Server.CreateObject("ADODB.Connection") ), Exactly on word Server.

Do I need to set any thing on Tools>References in VB Editor/

Best Regards
bijan
0
 
LVL 10

Expert Comment

by:avidya
ID: 11873193
Hi Bijan,

Lets just start to say I'm an idot.
I gave you part of asp code, not vb, sorry....
Hpe this works


Private Sub Command75_Click()
 Dim demoConn As ADODB.Connection
 Dim r2 As ADODB.Recordset
 Dim demoPath As String

 demoPath = "DRIVER={SQLServer};" & "SERVER=Bijan;UID=sa;" & "PWD=ba;DATABASE=Northwind"
' Open a connection.
 Set demoConn = New ADODB.Connection
 demoConn .Open demoPath
' Open a recordset based on a table.
 Set r2 = New ADODB.Recordset
 r2.Open "Table1", demoPath , , , adCmdTable
'something with records here

 rs2.Close
 demoConn.Close
End Sub


You can also open the recordset by an sql query:


Private Sub Command75_Click()
 Dim demoConn As ADODB.Connection
 Dim r2 As ADODB.Recordset
 Dim demoPath As String
 Dim strSQL As String

 demoPath = "DRIVER={SQLServer};" & "SERVER=Bijan;UID=sa;" & "PWD=ba;DATABASE=Northwind"
' Open a connection.
 Set demoConn = New ADODB.Connection
 demoConn .Open demoPath
' Open a recordset based on an SQL string.
 Set rs2 = New ADODB.Recordset
 strSQL = "SELECT field1, field2 from Table1"
 r2.Open strSQL, demoPath , , , adCmdText

'something with records here

 rs2.Close
 demoConn.Close
End Sub

0
 

Author Comment

by:Bijanbijan
ID: 11879530
Dear avidya
Thanks again for reply.
But It still is not working for me at the moment, I copied the exact coding from your reply, both of them and I get the same Error message:

Error: Run-time error-217467259 (80004005)’:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.
And the curser gets focused on demoConn.Open demoPath in VB Editor

Also didn’t we forgot to tell the program, what are the Source date and destination Fields? Some thing like “ r2("OrderID") = Me.OrderID
    r2("QoteID") = Me.QoteID ”



Best regards
Bijan Bijan

0
 
LVL 10

Expert Comment

by:avidya
ID: 11887263
Hi Bijan Bijan,

This error "Error: Run-time error-217467259 (80004005)’:....
means there is something wrong in the demopath string.
two questions:
1) do you have the sql odbc driver installed?
2) do you have an entry in your sql server that is called "northwind'?

This is the syntax for the connection string:
varname ="DRIVER={SQLServer};SERVER=TheServer;UID=TheUser;PWD=ThePassword;DATABASE=TheDatabase"

Youre code for ceating a new record, filling it and saving it looks fine to me, assuming that Me.OrderID and Me.qoteID are on your form:
  r2.AddNew
  r2("OrderID") = Me.OrderID
  r2("QoteID") = Me.QoteID

Hope this helps, blew up my computer, cannot test it for you, sorry

0
 

Author Comment

by:Bijanbijan
ID: 11889467
Hi avidya,

Regarding Your questions,

 1) do you have the sql odbc driver installed?
2) do you have an entry in your sql server that is called "northwind'?

Theses are the Info from my Mycomputer>Control Panel>Performance and maintenance>Administrative Tools>ODBC>
I am copying the Exact Info:
Microsoft SQL Server ODBC Driver Version 03.81.9030

Data Source Name: Northwind
Data Source Description:
Server: Bijan
Database: Northwind
Language: (Default)
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Integrated Security: Yes
Use Regional Settings: No
Prepared Statements Option: Drop temporary procedures on disconnect
Use Failover Server: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
Data Encryption: No

Microsoft SQL Server ODBC Driver Version 03.81.9030

Running connectivity tests...
Attempting connection
Connection established
Verifying option settings
Disconnecting from server
TESTS COMPLETED SUCCESSFULLY!

I think, As you can see it is installed and tested Successfully.
Could be the Username password problem? I can Login to my SQL analyzer with above username, and Password. But my Enterprise Manager does not ask for login at all.
Do you suggest that I post this question as a new thread?

Best regards
Bijan


0
 
LVL 10

Expert Comment

by:avidya
ID: 11894751
Hi Bijan

Yes, it is installed Successfully, so thats not the problem, good to know!

Lets give the demopath string one more try:
demoPath="DRIVER={SQLServer};SERVER=Bijan;UID=sa;PWD=ba;DATABASE=Northwind"


...But my Enterprise Manager does not ask for login at all....
I would turn the authorisation in EM on(safer annway)  and set the apropied rights for the above user and the test it again.


... Do you suggest that I post this question as a new thread?...
no, why?

Best regards
Avidya
0
 

Author Comment

by:Bijanbijan
ID: 11899993
Hi avidya
I received your reply a little late, and I went ahead and posted My (our)
Problem as a new Thread, I have seen that some times the smallest of mistake can make some Codes execute or not to execute.
Any how you may want to take look at them Posting By name of:
Error: Run-time error-217467259 (80004005)’:  (Data source Name Not Found)
But of course as of yet still no Success.
Also the same with your latest reply, Still is not execting.

Best Regards
Bijan
0
 

Author Comment

by:Bijanbijan
ID: 11904898
Hi avidya

I should tell you that my OS is Win XP, and at first I had only SQL server Developer edition Installed on my computer, and now I just changed it to Personal edition, and also I have only client tool installed on my local machine, and I do not have Server + Client tools installed on my computer. Is this going to make any difference?

Best Regards
Bijan
0
 
LVL 10

Expert Comment

by:avidya
ID: 11919629
Hi,

stupid question maybe, but did you add the ado component to your project?
I'm using vb6 and the its done by "project/components/microsoft ado ..
one your c: in youre windows/system32 dir there should be a msadodc.ocx.
It should be available on your webserver too.

IBM suggest checking this:
Verify that there is no space between Data Source= and the system name. Strip any leading blanks from the system name. Verify that the system name matches the name configured in Client Access Connections program.

this has various ways of connecting with ODBC:
http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm#ODBCDriverForSQLServer

this has various ways of connecting with OLE:
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForSQLServer

Have also found this code, maybe this will work?
You have to changes the values at Private Sub Class_Initialize() to your own.

=====
Dim Conn As Connection
' public variables for the server, database, user name and password
Public mServer as String
Public mDb As String
Public mUsr As String
Public mPwd As String

'The class is as under:
 


Private Sub Class_Initialize()
' initializing the class for the Server Name, Database name, user name and password for connection string
mDb = strDb
mServer= strServer
mUsr = strUserName
mPwd = strPwd
End Sub

Public Sub Connect()
Set Conn = New Connection
Conn.CursorLocation = adUseClient
' connection string for SQL Server, you will have to change only this connection &#8216;string for a different database
strConn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" + mUsr + ";" _
        + "Password=" + mPwd + ";Initial Catalog= " + mDb+ " ; Data Source=" + mServer + ""
Conn.Open strConn

End Function



' takes the query as a string and returns the opened recordset
Public Function GetRecordset (ByVal strQuery As String, rstResult As Recordset) As Boolean
Dim rst As New Recordset
Conn.CommandTimeout = 60
rst.Open strQuery, Conn, adOpenDynamic
If rst.RecordCount <= 0 Then
    GetRecordset = False
Else
    Set rstResult = rst
    GetRecordset = True
End If

End Function

' takes the query as a string and returns the forward only recordset using Command Object
Public Sub GetInfo(strQuery As String, ByRef rst As ADODB.Recordset)

Dim oCommand As New ADODB.Command
Dim strCmd As String
' specify the connection
oCommand.ActiveConnection = Conn
' build the command
strCmd = strQuery
oCommand.CommandText = strCmd
oCommand.CommandTimeout = 60
' execute the command
Set rst = oCommand.Execute

End Function

' takes a update or delete query  as string and updates the database
Public Sub UpdateIt(ByVal strQuery As String)

Dim oCommand As New ADODB.Command
Dim lngRecord As Long
Dim i As Integer
' specify the connection
oCommand.ActiveConnection = Conn
' prepare the sql command
oCommand.CommandText = strQuery
' execute the command
oCommand.Execute

End Function
     
Public Sub DisConnect()
Conn.Close ' close the connection
Set Conn = Nothing ' release the resources
End Sub



0
 
LVL 10

Expert Comment

by:avidya
ID: 11919645
and i just saw this one in anther post:
The latest MDAC drivers (anything in the past six months), and especially the Access ODBC driver, have a "feature" that REQUIRES a SYSTEM temp variable be establised. This is done from right clicking My Computer, choosing Properties, Environment, and adding the system variables of TMP C:\TEMP and TEMP C:\TEMP.
0
 

Author Comment

by:Bijanbijan
ID: 11922493
Hi  avidya
It is good to see your reply.
And Thanks a Ton for all the Info, Off Course It will take awhile for me to review them.
Another strange thing about the table that I made (Table1) in My Northwind database (Sample database of Microsoft) and also the form that I made using the access wizards (only 2 textboxes for entering data) is that, I just can not add any data from the form (The add Button in Navigation Bar is not activated at all), I have to add the data from the EM>northwind>Table1.
On the same Issue, when I want to enter data form that form I get the Following Error:
*Verify that OLE Server is registered correctly.
*Make sure your computer is corrected to the server on which the OLE server application resides.
*Close the OLE server and restart it out side of Microsoft Access. Then try the original operation again from with in Microsoft Access.

As you can see, I think we have OLE server problem among other things.

Best regards,
Bijan
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 10

Expert Comment

by:avidya
ID: 11922709
Seems like the form is having the same issue with a different message.
Maybe refresshing/recreating of the connection is needed.
Could be that the change from SQL server Developer edition to Personal edition is causing trouble.

Good luck
0
 

Author Comment

by:Bijanbijan
ID: 11925000
Hi
I do not thjink so, Since I made the form again after I changed my Installation.
Do you know where can I get the latest of OLE Collection? also where is the OLE Server on my Computer? How can i register my OLE server?
I have another problem, which is related to this one or is the same one.
What I wanted to do originally was to enter some data from 2 Text boxes on my order form to my Table1, which is not related to my Order form.
Since I am having to do this simple thing, I decided to add my Table1 to the query which is linked to my Order Form, Now my Order Query would end up with 3 Tables rather than 2. Is this a Practical procedure?
Now from my order form I added a Button to do data entry from my Order form to the Newly added third Table, But I get the Following Error:
“Only fields from unique table can be edited.”

Could you tell me what is wrong?

Best Regards
Bijan
0
 
LVL 10

Expert Comment

by:avidya
ID: 11925067
Hi Bijan,

Just read this in another question:
- Go to youre desktop
- create a new texfile
- name it x.udl
- doubleclick on it
- make youre connection
- test your connection
- save your connection
- open  x.udl with notepad
you will see the correct string for connecting.
- Copy and pase it after "demoPath="
This way we know for sure there isn't an error in the connection string.

As for your questions:
- Do you know where can I get the latest of OLE Collection?
- where is the OLE Server on my Computer?
- How can i register my OLE server?
OLE is a way of connection to data. Windows provides these services and has a couple of them on board.
Also some software provide their own drivers, like Oracle.
This can be a bit confusing since Microsoft delevers one also. Most times the specific driver form the third party will have more functionality.
You can see which drivers are installed by clicking
  "start/programs/administrive tools/ datasource (odbc)

- “Only fields from unique table can be edited.”
Your form doesn't know which field to update, because of the join you made.
This explains why:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac10/html/acproUniqueTable.asp






0
 

Author Comment

by:Bijanbijan
ID: 11925443
Hi avidya
I was going tos end you this comment that I saw your latest Comment.
Following error is fixed, I no longer get this error when I wanted to enter data from my Table1 form to table1 table.
Apparently it was some Indexing problem as soon as I added a key Index for the Table1 with in the EM itself, the problem was solved,
*Verify that OLE Server is registered correctly.
*Make sure your computer is corrected to the server on which the OLE server application resides.
*Close the OLE server and restart it out side of Microsoft Access. Then try the original operation again from with in Microsoft Access.

But I would like to know,
Where is the OLE server on my computer? How can I register it?
Do you know the URL location of the Latest OLE collection or codes?

Finally I am still getting this error:
Runtime error’-2147217887 (80040e21)’:
Multiple-step OLE DB Operation generated errors. Check each OLE DB status value, if available, No work was done. (When I run the following Procedure)

Private Sub Command75_Click()
  Dim demoPath As String
  Dim demoConn As ADODB.Connection
  Dim r2 As ADODB.Recordset

 Set demoConn = Server.CreateObject("ADODB.Connection")
 demoPath = "DRIVER={SQLServer};" & "SERVER=Bijan;Integrated Security=SSPI;DATABASE=Northwind;DSN=Northwind"
demoConn.Open demoPath
Set r2 = CurrentDb.OpenRecordset("Table1")
    r2.AddNew
    r2("OID") = Me.OID
    r2("QID") = Me.QID
   
    r2.Update
    r2.Close

End Sub



But I am trying a whole new Procedure could you tell what is wrong with this Procedure?

Private Sub Command79_Click()
Dim myCn As New ADODB.Connection
Dim myRs As New ADODB.Recordset
myCn.ConnectionString = "DSN=Northwind"
myCn.Open
myRs.Open "SELECT * FROM Table1", myCn, adOpenDynamic

INSERT INTO TABLE (OID, QID) VALUES (OID, QID);

End Sub

Best regards,
Bijan
0
 
LVL 10

Expert Comment

by:avidya
ID: 11925566
- Where is the OLE server on my computer? How can I register it?
You can see which drivers are installed by clicking
  "start/programs/administrive tools/ datasource (odbc)
and also make new connections here. If the test of the connection returns "ok"that means your ole server is correctly registrerd.
- Do you know the URL location of the Latest OLE collection or codes?
The standard Microsoft set is called MDAC, it is part of windowsUpdate. If not downloaded automaticly, you can find them here:
http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c&displaylang=en
For the third party drvers you can go to the third party or search, par example, google:
+ole +driver +download

As for your code:
INSERT is an sql statement, the syntax is;
SYNTAX: INSERT INTO {Tablename}({fieldname}[,{fieldname}]) VALUES ({value}[,{value}]) . so youre code should look like this.

Private Sub Command79_Click()
 Dim myCn As New ADODB.Connection
 Dim myRs As New ADODB.Recordset
 myCn.ConnectionString = "DSN=Northwind"
 myCn.Open
 myRs.Open "INSERT INTO TABLE1 (OID, QID) VALUES (OID, QID);", myCn, adOpenDynamic
End Sub

You can see an explanation of al the sysntaxes over here:
http://www.vbexplorer.com/VBExplorer/vb_feature/june2000/SQL_for_Beginners.asp

0
 

Author Comment

by:Bijanbijan
ID: 11926726
Hi avidya
Thanks a lot for the latest technique and Infos, It was very interesting.
Any how from that technique I came up with following line of codes (one among many that could be generated and Tested successfully.) (this is the simplest one)
demoPath = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=Northwind;Initial Catalog=Northwind"
I added the above line we passed our Famous Error, But now I get another Error:
“Run time 3251
Current Record set does not support updating. This may be a limitation of this provider or of the selected lock type.”
And the curser gets focused in VB Editor on:
r2.AddNew

Also for the reading you provided on Unique Tables or Properties (Also form the Access help on this issue) I came up with the following codes. (I should mention one way to do it is using the VB and the other way to do it is using the Index Button on Menu Bar or in EM itself, But since my table is actually a View (or query) Both of these Buttons are inactive therefore I had to use VB in order to Index my Fields in my latest query):
Please verify this for me.

Private Sub Command82_Click()
On Error GoTo Err_Command82_Click
Dim dbs As Database, tdf As TableDef
    Dim idx As Index, fldID As Field, fldID2 As Field

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Return reference to Products table.
    Set tdf = dbs.TableDef![dbo Orders Qry2]
    ' Return Index object that points to new index.
    Set idx = tdf.CreateIndex("IDN")
    ' Create and append index fields.
    Set fldID = idx.CreateField("OID")
    Set fldID2 = idx.CreateField("QID")
    idx.Fields.Append fldID
    idx.Fields.Append fldID2
    ' Set index properties.
    idx.IgnoreNulls = True
    idx.Unique = True
    ' Append new Index object to Indexes collection.
    tdf.Indexes.Append idx


     DoCmd.GoToRecord , , acNewRec
    OIDID.Value = OrderID.Value
    QIDID.Value = OrderID.Value + 10000
   Set dbs = Nothing
Exit_Command82_Click:
    Exit Sub

Err_Command82_Click:
    MsgBox Err.Description
    Resume Exit_Command82_Click
   
End Sub

Any how I still get an Error:
“Compil Error
Method or data member not found.”
And the curser gets focused on    Set tdf = dbs.TableDef![dbo Orders Qry2]

I do not understand this is an easy task in Access but how come it is so complicating in SQl server.

Best Regards
bijan

0
 
LVL 10

Expert Comment

by:avidya
ID: 11927211
Hi Bijan,

Glad we managed to get by the big connection bump...
so, lets move on...
----
Run time 3251 Current Record set does not support updating. This may be a limitation of this provider or of the selected lock type
---
If in the code for "Private Sub Command75_Click()" only the provider has changed then you're missing something.
By default, most providers open in readonly mode and you have to set  the following options to youre recordset for write access BEFORE you open the record set:
- CursorLocation
- CursorType
- LockType
aka:
r2.CursorLocation = adUseClient
r2.CursorType = adOpenStatic
r2.LockType = adLockBatchOptimistic

Here is a fine example, on the left menu you can find more options explained:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprocursortypex.asp    

------
Compil Error Method or data member not found.”
And the curser gets focused on    Set tdf = dbs.TableDef![dbo Orders Qry2]
------
First, I don't understand exacty what you are trying to accomplish with this query.
Can you explain it to me?
Furthermore, you can set indexes on a table, but i don't know if it can be done by a query. It's better to use the primary key from the table to uniquely identify records.

So, if you realy want to use indexes, set them directly to the table, NOT using a query.
The index is part of the table design and has to be only set once.

As I understand it, you are trying to add two new values which you type in the formfield "order.is and you want to append this value to the fields OIDID and  QIDID in an MS access table.
This can be done like this:

Private Sub Command82_Click()
 Dim dbs As Database
 Dim Tbl As Recordset
 Set dbs = CurrentDb
 Set Tbl = dbs.OpenRecordset("<tablename>", dbOpenDynaset)
 With Tbl
  .AddNew
  !OIDID = me![OrderID]
  !QIDID = me![OrderID] + 10000
  .Update
 End With
 Set Tbl = Nothing
 Set dbs = Nothing
 msgbox " update done"
End Sub
0
 

Author Comment

by:Bijanbijan
ID: 11929290
Hi Avidya
Fianally we had some success.
The following Codes work for values Constant, But I still can not make it work for Variables or expressions:

Private Sub Command79_Click()
 Dim myCn As New ADODB.Connection
 Dim myRs As New ADODB.Recordset
 
 myCn.ConnectionString = "DSN=Northwind"
 myCn.Open
 myRs.Open "INSERT INTO TABLE1 (OID, QID) VALUES (10001 , 10002)", myCn, adOpenDynamic
End Sub

The above works for constants, But it automatically sorts the Data entries from Column 1 as I enter data, and basically it does not enter as it is and sorts it Ascending in column 1.

But my main question is How can I Enter variables or Expressions?
For my values, I would like to enter the value of a TextBox or ComboBox , For example lets say I have Combobox by name of OrderID I would like to enter for my first value the value of ordered, and for my second value OrderID+10000.
I made the following codes for it but I keep getting an Error for it.

Private Sub Command79_Click()
 Dim myCn As New ADODB.Connection
 Dim myRs As New ADODB.Recordset
 Dim R As Integer
 Dim RR As Integer
 R = Me!OIDID   (OIDID and QIDID are 2 Textboxes)
 RR = Me!QIDID
 myCn.ConnectionString = "DSN=Northwind"
 myCn.Open
 myRs.Open "INSERT INTO TABLE1 (OID, QID) VALUES (R , RR);", myCn, adOpenDynamic
End Sub

Best Regards
Bijan
0
 

Author Comment

by:Bijanbijan
ID: 11929530
Hi avidya
About your questions I should say that All the tables are in SQL server, and basicly I am not in Access at all, I am just using Access in order to make my forms and Reports that is all, Therefore I do not think that Following codes will work for us:

Private Sub Command83_Click()
On Error GoTo Err_Command83_Click
DoCmd.GoToRecord , , acNewRec
Dim dbs As Database
 Dim Tbl As Recordset
 Set dbs = CurrentDb
 Set Tbl = dbs.OpenRecordset("Table1", dbOpenDynaset)
 With Tbl
  .AddNew
  !OIDID = Me![OrderID]
  !QIDID = Me![OrderID] + 10000
  .Update
 End With
 Set Tbl = Nothing
 Set dbs = Nothing
 MsgBox " update done"
Exit_Command83_Click:
    Exit Sub
Err_Command83_Click:
    MsgBox Err.Description
    Resume Exit_Command83_Click
    End Sub
As I ran the above odes I get the Compiler Error and gets focused on
“dbs.OpenRecordset”

About the first Part of your comment, which is the one I think we should concentrate on “In this Approach off course.”
I made the following codes:
 
Private Sub Command74_Click()
Dim demoConn As ADODB.Connection
 Dim r2 As ADODB.Recordset
 Dim demoPath As String
 r2.CursorLocation = adUseClient
 r2.CursorType = adOpenStatic
 r2.LockType = adLockBatchOptimistic

demoPath = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=Northwind;Initial Catalog=Northwind"

' Open a connection.
 Set demoConn = New ADODB.Connection
 demoConn.Open demoPath
' Open a recordset based on a table.
 Set r2 = New ADODB.Recordset
 r2.Open "Table1", demoPath, , , adCmdTable
r2.AddNew
r2("OID") = Me.OIDID
  r2("QID") = Me.QIDID
 r2.Close
 demoConn.Close

End Sub

And I get thid Error:
Run-Time error ‘91’:
Object Variable or with block variable not set.
And get focused on
“r2.CursorLocation = adUseClient”

About the forms, Basically I want to insert data form a form, which is linked to a query itself in to another table (Table1), which is not linked to any other forms. And then I would like to use this Table1 and added to my original query and run a new query, which will be made of 3 tables.
But I say let’s solve this Basic problem of entering data from a Form to a Not linked Table, Then we can go to next step. Off course possibly if we could enter the data to my final query, which is made up of three tables would be the more proper way of solving this problem, If you remember I asked you about this Process earlier.

Best Regards
Bijan
0
 
LVL 10

Expert Comment

by:avidya
ID: 11929823
Hi Bijan,

Slowly we are getting there!

one note, as I sad before, it would speed thing up if you place a discription of for table(s) over here like
Fieldname   fieldtype
OrderID       autonumber
OID             nummeric
QID             nummeric
product        memo
etc..

as for your code, with .... myRs.Open "INSERT INTO.... you are creating a string.
In your string R and RR are sean as text, not the vars the are.
This is the correct string to make a combination of text and vars:
("INSERT INTO TABLE1 (OID, QID) VALUES (" & R & "," & RR & ");")

I wrote you new code for Private Sub Command79_Click().
Using connectionstrings this way is for databases outside the one youre working in.
(see the comments in the code for explanation)

I also wrote code for adding an new record in the database youre working in:
Private Sub Command100_Click()
(see the comments in the code for explanation)

Avidya

===============================================
Private Sub Command79_Click()
  Dim myCn As New ADODB.Connection
  Dim Cmd As New ADODB.Command
  Dim myRs As New ADODB.Recordset
  Dim R As Integer
  Dim RR As Integer
  Dim error_fld As String
  Dim msg_warning As String
 
  error_fld = ""
  'check for entered values
  If IsNull(Me!OIDID) And IsNull(Me!OIDID) Then
   error_fld = "OIDID and QIDID"
  ElseIf IsNull(Me!OIDID) Then
   error_fld = "OIDID"
  ElseIf IsNull(Me!QIDID) Then
   error_fld = "QIDID"
  Else
   R = Me!OIDID
   RR = Me!QIDID
  End If
  If Len(error_fld) > 0 Then
   msg_warning = "you have to give a value for " & error_fld & "."
   MsgBox msg_warning
  Else
'create dns-less msaccess connection string
  'DB_PATH = "DBQ=" & "E:\expertexchange\db1.mdb" & ";Driver={Microsoft Access Driver (*.mdb)}"
'create dns connection string
   DB_PATH = "DSN=Northwind"
'Set connection string
   myCn.ConnectionString = DB_PATH
'open connection
   myCn.Open
'Set the recordset opening type
   myRs.CursorLocation = adUseClient
   myRs.CursorType = adOpenStatic
   myRs.LockType = adLockBatchOptimistic
'Make connection active and create a new Record by executing an SQL statement.
   Set myRs = myCn.Execute("INSERT INTO TABLE1 (OID, QID) VALUES (" & R & "," & RR & ");")
   myCn.Close
  End If
 Set myRs = Nothing
 Set myCn = Nothing
End Sub
===============================================
Private Sub Command100_Click()
'add a new record in the database youre working in
'surpressing screenmessages
 DoCmd.SetWarnings False
  Dim R As Integer
  Dim RR As Integer
  Dim error_fld As String
  Dim msg_warning As String
 
  error_fld = ""
  'check for entered values
  If IsNull(Me!OIDID) And IsNull(Me!OIDID) Then
   error_fld = "OIDID and QIDID"
  ElseIf IsNull(Me!OIDID) Then
   error_fld = "OIDID"
  ElseIf IsNull(Me!QIDID) Then
   error_fld = "QIDID"
  Else
   R = Me!OIDID
   RR = Me!QIDID
  End If
  If Len(error_fld) > 0 Then
   msg_warning = "you have to give a value for " & error_fld & "."
   MsgBox msg_warning
  Else
   DoCmd.RunSQL ("INSERT INTO TABLE1 (OID, QID) VALUES (" & R & "," & RR & ");")
  End If
  'activating screenmessages
  DoCmd.SetWarnings True
End Sub



0
 
LVL 10

Expert Comment

by:avidya
ID: 11930117
Hi Bijan,
sorry, just saw your last post, crossed mine.
so lets update
- my Code for Private Sub Command79_Click() should work now
-  code for Private Sub Command100_Click() is irrelevant, you are not using an msaccess table
- code for Private Sub Command83_Click() is irrelevant, you are not using an msaccess table

As for Private Sub Command74_Click(), you first have to SET r2 before you can work with it.
It good practice to first Dim, then Set and then Fill vars.
After you're finnished with them, you set them to 'Nothing' so they don't take up anymore space.
By using "Dim xx As New yyy" you dim and set the space for the var, you only have to fill them.

This is the corrected code:
=====================
Private Sub Command74_Click()
  Dim demoConn As New ADODB.Connection
  Dim sqlcmd As New ADODB.Command
  Dim r2 As New ADODB.Recordset
'create dns-lessconnection string
  demoPath = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=Northwind;Initial Catalog=Northwind"
'Set connection string
   demoConn.ConnectionString = demoPath
'Open connection
   demoConn.Open
'Set active connection
   Set sqlcmd.ActiveConnection = demoConn
'Set sql statement
   sqlcmd.CommandText = "Select * From TABLE1;"
'set cursorlocation
   r2.CursorLocation = adUseClient
'open recordset for editing
   r2.Open sqlcmd, , adOpenKeyset, adLockOptimistic
'adding new record
   r2.AddNew
'filling records
   r2("OID") = Me.OIDID
   r2("QID") = Me.QIDID
'update record
   r2.Update
'closing recordset
   r2.Close
'closing connection
   demoConn.Close
'destroing Set's
   Set r2 = Nothing
   Set demoConn = Nothing
   Set sqlcmd = Nothing
End Sub
0
 

Author Comment

by:Bijanbijan
ID: 11938874
Hi avidya
Congratulationnnn! Finally success.
Both procedure work just fine now.
Just few notes on them, On the second procedure your code was missing a line, you forgot to Dim the value for demoPath:
“Dim demoPath As String”
The above line needs to be added to your last given codes and then it will work just fine.
Just another final note on this issue: As you know we could enter Duplicate values in our Table1 Since I am using a cobmobox in order to fill them as we enter duplicate I get the following Error:
Run-time Error 2147217900 (80040e14)’
Duplicate key was ignored.
Before we get this error I would like to check the values in my Table1 and if it is the same as the value we are about to enter, I would like to send for example this Message box “Order ID Has Been Issued Already” And basically prevent this entry to be happening.

Best Regards
Bijan
0
 
LVL 10

Accepted Solution

by:
avidya earned 125 total points
ID: 11944788
Hi Bijan,

Great!
Stupid me, I deleted that line, sorry.
On to the next part, ok?

I wil add code to Private Sub Command74_Click() so it will check for duplicates and post it here, if thats allright with you.

BRT,

Avidya
0
 
LVL 10

Expert Comment

by:avidya
ID: 11945354
HI Bijan,

Here is the code, handels errors now, Added checking for not entering a value also.
See comments in code for explanation


=============

Private Sub Command74_Click()
'telling the routine what to do if an error raises
 On Error GoTo ErrHandler
  Dim demoConn As New ADODB.Connection
  Dim sqlcmd As New ADODB.Command
  Dim r2 As New ADODB.Recordset
  Dim demoPath As String
  Dim v_OIDID As Integer
  Dim v_QIDID As Integer
  Dim error_fld As String
  Dim msg_warning As String
 
  error_fld = ""
  'check for entered values
  If IsNull(Me!OIDID) And IsNull(Me!QIDID) Then
   error_fld = "OIDID and QIDID"
  ElseIf IsNull(Me!OIDID) Then
   error_fld = "OIDID"
  ElseIf IsNull(Me!QIDID) Then
   error_fld = "QIDID"
  Else
   v_OIDID = Me!OIDID
   v_QIDID = Me!QIDID
  End If
  If Len(error_fld) > 0 Then
   msg_warning = "you have to give a value for " & error_fld & "."
   MsgBox msg_warning
  Else
'create dns-lessconnection string
   demoPath = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=Northwind;Initial Catalog=Northwind"
'Set connection string
   demoConn.ConnectionString = demoPath
'Open connection
   demoConn.Open
'Set active connection
   Set sqlcmd.ActiveConnection = demoConn
'Set sql statement
   sqlcmd.CommandText = "Select * From TABLE1;"
'set cursorlocation
   r2.CursorLocation = adUseClient
'open recordset for editing
   r2.Open sqlcmd, , adOpenKeyset, adLockOptimistic
'adding new record
   r2.AddNew
'filling records
   r2("OID") = v_OIDID
   r2("QID") = v_QIDID
'update record
   r2.Update
'closing recordset
   r2.Close
'closing connection
   demoConn.Close
'destroing Set's
   Set r2 = Nothing
   Set demoConn = Nothing
   Set sqlcmd = Nothing
'Exit sub needed here so Errhandler is skipped
   Exit Sub
'Handling the errors
ErrHandler:
 Select Case Err.Number
  Case 3219 'operation not allowed
    Resume Next
  Case -2147217900 'duplicate record
    MsgBox "Order ID Has Been Issued Already"
    Resume Next
  Case Else 'unknown error
    MsgBox "Unknown error:" & Err.Number & " " & Err.Description
    Resume Next
  End Select
 End If
End Sub
0
 

Author Comment

by:Bijanbijan
ID: 11949181
Hi avidya
Thanks so much for your latest reply, By the way since I am new to this forum I didnot realy know about the accepting Answers and so on, as I was readfing other Postings I found out about them, Any how I have Accepted your Answers.
By the way could you provide your email? Incase I needed to conact you directly "just incase that I can not find you in this Forum".
by the way I am posting another question you may want to look at it.

Best Regards
Bijan
0
 
LVL 10

Expert Comment

by:avidya
ID: 11950969
Hi Bijan,

So I guess it all works now and thanks for the points/grade!

As for providig my email, I don't, EE is all about sharing among each other and using email isn't.
I knew you where new, so I helped you where ever I could.

I would realy like you to read the section of the membership agreement in this link: http://www.experts-exchange.com/Databases/MS_Access/help.jsp#hs8

While reading it myself I saw that I was ignoring the "Asking a number of questions in one question"- rule myself
Could be that I gave you the wrong impression with that, since I answed several questions in this question.

Best regards and happy programming days!

Avidya




0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

19 Experts available now in Live!

Get 1:1 Help Now