By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

object not defined/odbc with msaccess

P: 11
hi

i 'm writng a code using msaccess with odbc.
my dsn name is htgry
i want to insert data eneterd in text fields to a table named qwer on clicking a command button.

code as follows

Private Sub Command14_Click()


Dim dbLocation 'Location of our Access database file
Dim sSQL 'Our SQL query

dbLocation = "C:\Documents and Settings\Administrator\Desktop"
Set objADO = dbLocation.CreateObject("ADODB.Connection") 'Create an ADO connection to a database //// line1

objADO.Open "htgry" //// line2
objADO.Open dbLocation 'Open the database
sSQL = "INSERT INTO qwer ( name , amount ) VALUES('" & Text7 & "' , '" & Text9 & "')" 'Set our SQL query
objADO.Execute (sSQL) 'Execute this SQL query


objADO.Close
Set objADO = Nothing

End Sub

it is not being executed and error is "object not defined " at line 1 and line 2

kindly make me complete this programme error free.
with anticipation
Nov 24 '06 #1
Share this Question
Share on Google+
6 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
The syntax for creating DSN connections is as follows:
Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. Dim conConnector As ADODB.Connection
  4.  
  5.     Set conConnector = New ADODB.Connection
  6.  
  7.     conConnector.Open "DSN=htgry;UID=UserName;PWD=Password;"
  8.  
  9.     conConnector.Close
  10.     Set conConnector = Nothing
  11.  
  12.  
  13.  
Nov 25 '06 #2

P: 11
ihav euse dyour code
following error occured
run time error 2147467259(80004005)
[Microsoft]ODBC microsoft access driver] the database has been placed in a state by an unknown user that prevents it from being opened or locked
dsn
code:

Dim dbLocation 'Location of our Access database file
Dim sSQL 'Our SQL query
Dim conConnector As ADODB.Connection

Set conConnector = New ADODB.Connection

conConnector.Open "DSN=htgry;UID=UserName;PWD=Password;"
dbLocation = "C:\Documents and Settings\Administrator\Desktop\dbd"
conConnector.Open dbLocation
sSQL = "INSERT INTO Table1 ( name , amount ) VALUES('" & Text7 & "' , '" & Text9 & "')" 'Set our SQL query
conConnector.Execute (sSQL) 'Execute this SQL query
conConnector.Close
Set conConnector = Nothing

what is password, userid?
hoping reply



The syntax for creating DSN connections is as follows:
Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. Dim conConnector As ADODB.Connection
  4.  
  5.     Set conConnector = New ADODB.Connection
  6.  
  7.     conConnector.Open "DSN=htgry;UID=UserName;PWD=Password;"
  8.  
  9.     conConnector.Close
  10.     Set conConnector = Nothing
  11.  
  12.  
  13.  
Nov 27 '06 #3

P: 11
ihav euse dyour code
following error occured
run time error 2147467259(80004005)
[Microsoft]ODBC microsoft access driver] the database has been placed in a state by an unknown user that prevents it from being opened or locked
dsn htgry, filename dbd, table name Table1.
code:

Dim dbLocation 'Location of our Access database file
Dim sSQL 'Our SQL query
Dim conConnector As ADODB.Connection

Set conConnector = New ADODB.Connection

conConnector.Open "DSN=htgry;UID=UserName;PWD=Password;"
dbLocation = "C:\Documents and Settings\Administrator\Desktop\dbd"
conConnector.Open dbLocation
sSQL = "INSERT INTO Table1 ( name , amount ) VALUES('" & Text7 & "' , '" & Text9 & "')" 'Set our SQL query
conConnector.Execute (sSQL) 'Execute this SQL query
conConnector.Close
Set conConnector = Nothing

what is password, userid?
hoping reply



The syntax for creating DSN connections is as follows:
Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. Dim conConnector As ADODB.Connection
  4.  
  5.     Set conConnector = New ADODB.Connection
  6.  
  7.     conConnector.Open "DSN=htgry;UID=UserName;PWD=Password;"
  8.  
  9.     conConnector.Close
  10.     Set conConnector = Nothing
  11.  
  12.  
  13.  
Nov 27 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
You said you created a DSN to another database. Does that database have a userid and password? If not then just put "DSN=htgry;"
Nov 27 '06 #5

P: 11
hello

i used ur code
error: not a valid password
wat is password and how to overcome this error

pls reply

The syntax for creating DSN connections is as follows:
Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. Dim conConnector As ADODB.Connection
  4.  
  5.     Set conConnector = New ADODB.Connection
  6.  
  7.     conConnector.Open "DSN=htgry;UID=UserName;PWD=Password;"
  8.  
  9.     conConnector.Close
  10.     Set conConnector = Nothing
  11.  
  12.  
  13.  
Dec 8 '06 #6

NeoPa
Expert Mod 15k+
P: 31,656
hello

i used ur code
error: not a valid password
wat is password and how to overcome this error

pls reply
It sounds like you need to have a word with your IT department.
Ask them to explain this concept to you and give you the details you need to access via this DSN.
Dec 8 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.