Solved INSERT INTO from DBF File

[EzGoingKev]

I imported a DBF into Access as a local table. I am trying to update the data using this:

INSERT INTO local_table_name SELECT * FROM [DBF_file_name.DBF] IN '\\ServerName\FolderName'

I am getting an error : "The Microsoft Access database engine cannot open or write to the file '\\DAPPARTSCAT01\Vertical\BBB2\DATA-Turbo'. It is already opened exclusively by another user, or you need permission to view and write its data."

I have a macro that pulls data from an Access table in an Access database in the same location and that works fine. I figure it has to do with the fact that it is a DBF file. I have tried a few iterations of the above statement with the DBF removed from the file name and the DBF file name added to the location but none seem to work.

Can anyone help me out?

 

[theDBguy]

Hi. Just curious, is there any way you could share with us a sample copy of the DBF file, so we can give it a try?

 

[EzGoingKev]

I do not think my company would like that.

 

[theDBguy]

I do not think my company would like that.

Hi. A sample copy simply means a DBF file with test data. The reason why I ask for it is because I have no way to generate one, so I can't help you test it. Do you think you're company would object to that (you generating a sample DBF file for us to help you)?

In other words, we don't need to see your actual data or any sensitive company information, but we do need a sample file to help you.

PS. I guess when I say "we," I meant me. Perhaps others here have their way of creating a DBF file and be able to help you. Cheers!

 

[pbaldy]

Can you link to the file? I interact with FoxPro dbf files by linking to them in Access. Then it's a simple query

INSERT INTO local_table_name SELECT * FROM LinkedTableName

If memory serves other platforms use the dbf extension so I assume you'd have to use the driver appropriate to your provider. I use the old MS Visual FoxPro driver.

 

[EzGoingKev]

Can you link to the file? I interact with FoxPro dbf files by linking to them in Access.

I currently have the DBF files as linked tables. This is causing a couple of issues.

I am not the only person that uses the software that uses the DBF tables. I can either get locked out/or lock someone out.

The DBF files have numerical values stored as text. It would make my life much easier to have set the fields up as numerical.

IDK if it is our crappy network, our server, the way Access reads the DBF files, or what but the everything in the database hangs up like crazy when you go to do anything that has one of the linked DBF tables in it. Opening the Design View of a query takes forever. I went to type "Is Null" in the criteria section of a query and it hangs up for several minutes before I can type anything.

I do not need the data in the tables to be live, just updated once changes are made.

 

[pbaldy]

I understand, I actually do something similar but I copy the data into a SQL Server table since the rest of my data is there. I then use the SQL Server table in joins and such. I'm just throwing things against the wall here hoping something sticks. Do you specify key fields when you link? You may try linking without specifying key fields, which would make the linked table read only in Access. That might avoid the locking issues. Another thought is not linking permanently, but having your process link programmatically, run your insert SQL, then delete the linked table.

 

[Gasman]

If the Foxpro table is opened shared, you should both be able to use it and not lock anyone else out.?
You would be read only though, like linked excel files I believe?

 

[EzGoingKev]

I thought I would be able to just insert them into a table like with an Excel file or an Access table.

I can add the table to the database using DoCmd.TransferDatabase. I think I might use that to create a temp table, insert into another table and then delete the temp table. I was hoping for something with less moving parts.

 

[EzGoingKev]

If the Foxpro table is opened shared, you should both be able to use it and not lock anyone else out.?
You would be read only though, like linked excel files I believe?

If someone has the program open that uses the DBF tables I get a pop up that says I do not have permission to access them.

If I am accessing any data from one of the DBF tables and someone goes into the program they get all kinds of error notifications from within the program.

 

[Gasman]

That will likely because they have opened the dbf exclusive, which is the default?

I've just tried it on my computer and can update the dbf and see the changes in Access, I cannot update the linked table.?
If you have it open and they try exclusive, they will get an error message, likewise for you if they have it open exclusive.

Try it shared.

I'm not sure about the syntax you are using?

 

[EzGoingKev]

I really do not have any experience with FoxPro but when I open the database there is a bar at the bottom. In the lower left hand corner it gives me what record I am in / total record count just like Access. To the right of that it says "SHARED" so I believe the file is not set to Exclusive.

 

[Gasman]

Hmm, I do not get that, but if it says shared then that should be it?
You can also use

USE c:\users\paul\db\vfe7\mp3list.dbf shared

in the code window which is what I do?

 

[EzGoingKev]

@theDBguy

I found one that I use that does not have anything anyone would worry about.

 

[theDBguy]

@theDBguy

I found one that I use that does not have anything anyone would worry about.

Hi. Thanks for posting a sample file. I was able to try it out using the following SQL, and it worked!

insert into qualifie select * from [dBASE III;HDR=NO;IMEX=2;ACCDB=YES;DATABASE=c:\thedbguy\testing\].[qualifie]

Hope that helps...

 

[EzGoingKev]

That did it! You guys are awesome. This makes my day much easier.

 

[theDBguy]

That did it! You guys are awesome. This makes my day much easier.

Hi. You're welcome. We were all happy to assist. Good luck with your project.

 

[EzGoingKev]

Hi. Thanks for posting a sample file. I was able to try it out using the following SQL, and it worked!

insert into qualifie select * from [dBASE III;HDR=NO;IMEX=2;ACCDB=YES;DATABASE=c:\thedbguy\testing\].[qualifie]

Hope that helps...

I tried doing this here -

strPath = c:\thedbguy\testing\

And tried several different iterations of this -

insert into qualifie select * from [dBASE III;HDR=NO;IMEX=2;ACCDB=YES;DATABASE=strPath].[qualifie]

Can it be done or does it need the path typed out for each DBF file?

 

[theDBguy]

I tried doing this here -

strPath = c:\thedbguy\testing\

And tried several different iterations of this -

insert into qualifie select * from [dBASE III;HDR=NO;IMEX=2;ACCDB=YES;DATABASE=strPath].[qualifie]

Can it be done or does it need the path typed out for each DBF file?

Were you doing that in the Query Designer or in VBA?

 

[EzGoingKev]

VBA