[RESOLVED] Access 2003 - Insert Record

[nolim8ts]

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.

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

[RobDog888]

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.

[si_the_geek]

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)

[nolim8ts]

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:

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

Non-Working Connection String

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

[si_the_geek]

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

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

[RobDog888]

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

[nolim8ts]

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

Everything is being written / designed within Access

[RobDog888]

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?

[KGComputers]

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

Greg

[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

[nolim8ts]

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:

rs.Open stSQL, CurrentProject.Connection

Cheers to everyone that helped

[RobDog888]

You should use the CurrentDb instead of the CurrentProject.

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

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