Hello,
(I posted about this a few weeks agao, but got no response. I've tried some
more stuff since then...)
I’ve written a Visual basic .NET program that need to create a new .DBF file
based upon two existing .DBF files. The program generates an “Index not
found” exception when I attempt to create the new file using the SQL- SELECT
… INTO command.
However, this problem exists ONLY when run on a computer that is a member of
a domain . The problem occurs on the domain when run both over a network
share or locally on the machine.
When run over a peer to peer network, the program always behaves as expected
and successfully completes the SQL command, and creates the file (even when
run across the network).
NOTE: domain vs. peer2peer is the most apparent thing. The domain computers
are a windows 2003 server and a windows 2000 pro. The peer2peer is a
windowsXP box.
I have tried using a recordset and adding records to that, but it fails to
create the recordset if the .dbf file does not yet exist. Creating the
recordset does not create the file, like the SELECT..INTO command does.
I am aware of the need to adjust .NET security trusts on the assembly, and
have already overcome that issue, grating it full trust.
I have insured that the issue is not a NTFS permission or Share permission
issue, by granting EVERYONE full access to the folder in which the program
runs and tries to create the file. I also ran the program as domain
administrator, just to make sure.
My guess it that the problem does not necessarily lie in the code because it
only happens on a domain. None the less, here is a code except: showing the
connection string and SQL command. All the Add_log_entry commands show that
the correct data is in use, and that the exception is “Index not found.”
Dim NETConn As OleDbConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=.\\;" & _
"Extended Properties=""DBASE IV;"";")
NETConn.Open()
Dim myCommand As New OleDbCommand
myCommand.Connection = NETConn
…
some queries here that GET data from the connection... these work and setup
values for the variables used below
…
Dim create_string = "SELECT [CTtemp#DBF].*, [SYSPROF1#DBF].SYSCOMP AS cocode "
create_string += "INTO [" + Dest_File_Name + "#dbf] FROM [CTtemp#DBF],
[SYSPROF1#DBF] "
create_string += "WHERE ((([SYSPROF1#DBF].SYSCOMP)='" + CompanyCode +
"'));"
Add_log_Entry("Attempting to create Destination Table", MsgBoxStyle.OKOnly,
"Progress")
Add_log_Entry(create_string, MsgBoxStyle.OKOnly, "SQL Command")
myCommand.CommandText = create_string
Try
myCommand.ExecuteNonQuery()
Catch e2 As Exception
Add_log_Entry(e2.Message, vbOKOnly, "Exception")
Add_log_Entry("Error creating final table using Source file: " +
CompanyCode + TableName.Text + ".DBF", vbOKOnly, "Error")
GoTo Source_Loop_End
End Try