Run Time Error 3024

[dccjr]

I have a split database. On one of my forms, I am attempting to INSERT the values from multiple textbox into a table. With a seperate INSERT statement I am inserting other values to another table. When I click my "Execute" command button I am getting:

Run Time Error 3024 "Can not find file 'C:\Documents and Settings\username\My Documents\NADCAPdbBE.mdb".

I have tried deleting and relinking the tables, but still have the problem. My BE is not even on the C:. I am connecting to a drive on the server. The error is coming on my CurrentDb.Execute line for the first INSERT SQL. I have used this same setup in another split database with no problems. The path for linking the tables in the Linked Table Manager is correct. As an additional note, my FE is not on the C: either.



Why is this insisting that something be on C:?

Any insights would be of great help.

OK, I have found 3 other posts with a similar problem (on this forum and a couple others). Believe it or not, none have any replies. Microsoft is no help (not surprising).

Help me Obi Wan, you're my only hope!

[Beetle]

Can you post the SQL of your insert statement?

[ssanfu]

Why is this insisting that something be on C:?

Without seeing the code, it is hard to say..

From the database window, can you manually open the linked table(s)?
Do you have a reference set for Microsoft DAO 3.6 Object Library?

[dccjr]

Here is the code for the Add command button. I do have a reference set for the DAO library. From the database window I can open the table.

Private Sub cmdAddMaterialSet_Click()
Dim strMaterialInsertSQL As String
strMaterialInsertSQL = ""
    strMaterialInsertSQL = "INSERT INTO NADCAPdbBE.Materials (WPSNo, SetNo, PartNo, GroupNo, GroupTo, Alloy, AlloyTo, HTCond, HTCondTo, Thickness, ThicknessTo, FormSheet, FormSheetTo, Thickest, Thinnest)" _
                            & "VALUES ('" & Me.tbxWPSNo.Value & cenSQL & Me.tbxMatSetID.Value & cenSQL & Me.tbxPartNo.Value & cenSQL & Me.tbxGroupNo.Value & cenSQL & Me.tbxGroupToNo.Value & cenSQL & Me.tbxAlloyNo.Value & cenSQL & Me.tbxAlloyToNo.Value & cenSQL & Me.tbxHTCon.Value & cenSQL & Me.tbxHtConTo.Value & cenSQL & Me.tbxthickness.Value & cenSQL & Me.tbxThicknessTo.Value & cenSQL & Me.tbxFormSheet.Value & cenSQL & Me.tbxFormSheetTo & cenSQL & Me.tbxThickest.Value & cenSQL & Me.tbxThinnest.Value & endSQL
    CurrentDb.Execute strMaterialInsertSQL
    
    Me.tbxMatSetID = Me.tbxMatSetID.Value + 1
    Me.tbxGroupNo.Value = ""
    Me.tbxGroupToNo.Value = ""
    Me.tbxAlloyNo.Value = ""
    Me.tbxAlloyToNo.Value = ""
    Me.tbxHTCon.Value = ""
    Me.tbxHtConTo.Value = ""
    Me.tbxthickness.Value = ""
    Me.tbxThicknessTo.Value = ""
    Me.tbxFormSheet.Value = ""
    Me.tbxFormSheetTo.Value = ""
    Me.tbxThickest.Value = ""
    Me.tbxThinnest.Value = ""
End Sub

Not only is the thrown error supposedcly looking at C:, but it is also looking for an .mbd file extension. Yet when I split the tables, it saves them as an .accdb file. Is there a way to change this default? I really don't want to scrap this and recode everything. Is it more reliable to create an ADO connection? I have opened and edited records via ADO, but never inserted a new one. Is this something I should consider?

UPDATE: I have made some changes. The same error is occuring, but now with the correct drive and file name. However, it is still looking for an .mdb file extension.

[June7]

Do the cenSQL and endSQL variables contain apostrophes? Are all the destination fields text type?

The .Value property is not needed, Value is default.

[ssanfu]

As an additional note, my FE is not on the C: either.

Where is the FE located?

What are cenSQL & endSQL?

Add this line before the Currentdb line

Debug.Print strMaterialInsertSQL
  CurrentDb.Execute strMaterialInsertSQL

Set a breakpoint on the Currentdb line. Please post the result of the debug statement.

Why do you have the dB name before the table name? "Materials" is the linked table you want to insert the values into? As long as the table is local or linked, I've never had to add the database name.

Do the text and date fields (if any) have the proper delimiters? (Number type fields do not require a delimiter)

[June7]

Steve, think you meant: I've never had to add the database name. Good catch, am betting that is why the code is looking for a database file. Since there is no path specified, Access does the best it can and looks in MyDocuments

I think those variables hold comma and apostrophe delimiter string for separating the VALUES.

[ssanfu]

@June, thanks... you're right & I have corrected it.

I guessed the same... but it *appears* that most the fields would be number types. Must be global assignments....

[dccjr]

Both of you guys were correct. I have added the delimiters for DATE. I removed the .Value on the fields. June7 is absolutely corrent, the cenSQL and endSQL hold the comma/apostrophe combinations for joining VALUES. I removed the database name from the Table designation in the SQL.

The output from the debug is:

INSERT INTO Materials1 (WPSNo, SetNo, PartNo, GroupNo, GroupTo, Alloy, AlloyTo, HTCond, HTCondTo, Thickness, ThicknessTo, FormSheet, FormSheetTo, Thickest, Thinnest)VALUES ('','1','','','','','','','','','','','','','');

And it is absolutely correct!!!! It works great now. Last night this problem almost drove me to drinking. Thank you so much June7 and ssnafu. I really appreciate it.

[June7]

Glad it works now. But if any fields are number or date type, the sql will error with apostrophe delimiters.

The .Value property isn't needed but shouldn't hurt, just to save yourself some extra typing in the future.