[RESOLVED] Access 2003 - Insert Record

Hi All,

I can trying to insert a record from a form using a button's onclick event but have run into some problems.

Problem 1:

When the onclick event is fired, I am prompted with an error message stating that the access database file can not be found in my documents. How do I over come that problem?

Problem 2:

It's not really a problem but more like a I have forgotten the syntax. When creating the insert syntax, I can't remember how to specify the textboxes as values. You can see from my code below.

Code:

---

Private Sub btnCreateEmp_Click()

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim sSQL As String
Dim strConString As String

Set cn = New ADODB.Connection
Set cmd = New ADODB.Command

strConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Client Details.MDB; Persist Security Info=False;Mode=Read|Write"

cn.ConnectionString = strConString
cn.Open

sSQL = "INSERT INTO EmployeeDetails ([FirstName]) VALUES ('Me!FNAME')"

With cmd
.ActiveConnection = cn
.CommandText = sSQL
.CommandType = adCmdUnknown
.Execute
End With

Set cmd = Nothing
cn.Close
Set cn = Nothing

End Sub

---

You didnt specify the complete path so it doesnt know where its located.

Data Source=Client Details.MDB;

Should be ...

Data Source=" & App.Path & "\Client Details.MDB;

If the database is in the same folder as the exe.

For problem 2, see the article about it in the SQL section of our Database Development FAQs/Tutorials (at the top of the Database Development forum)

Thanks RobDog888 and si_the_geek for your replies. I can now insert records using a RecordSet but I am still having problems with the connection string.

Working Connection String:

Code:

---

conConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ClientDetails.MDB; Persist Security Info=False;Mode=Read|Write"

---

Non-Working Connection String

Code:

---

conConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\ClientDetails.MDB;Mode=Read|Write

---

When using the above connection string, a Run-Time error '424': Object Required is returned

OK, well in that case I guess you aren't using Classic VB.

Are you doing this inside Access itself (thus VBA)?

Sounds like it may be in Access but trying to connect to a secondary access database.

Quote:

---

Are you doing this inside Access itself (thus VBA)?

---

Everything is being written / designed within Access

Thread Moved from the VB 6 Forum to the Office Dev. Forum

Ok since its not VB 6 the App.Path wont work as its not supported in VBA.
Are you trying to connect to a separate database or the same one the VBA code is in?

I think he's just making the code within the database.....

Greg:) :) :)

Yes but if hes trying to connect to the same database then thats not needed as you can simply do a ...

Application.CurrentDb.Connection

Quote:

---

Originally Posted by RobDog888

Yes but if hes trying to connect to the same database then thats not needed as you can simply do a ...

Application.CurrentDb.Connection

---

The code is within the database. I now have it working using:

Code:

---

rs.Open stSQL, CurrentProject.Connection

---

Cheers to everyone that helped :wave:

You should use the CurrentDb instead of the CurrentProject.

Quote:

---

You can use the CurrentProject property to access the CurrentProject object and its related collections, properties, and methods.

---

Quote:

---

The CurrentDb method returns an object variable of type Database that represents the database currently open in the Microsoft Access window.

---