>>>Based on my research a command of this type should work to import data from an Access database.
>>>
>>>insert into LookupFundTypes (FundType)
>>>select fundtype
>>>from opendatasource ('Microsoft.Jet.OLEDB.4.0','D:\tipconversion.mdb')...fundtypes
>>>
>>>However when I run it I get an error:
>>>Msg 7403, Level 16, State 1, Line 6
>>>The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.
>>>
>>>I have win7 32 bit and sqlserver 2008 R2 installed.
>>>Can someone suggest a solution?
>>>Alternatively, is there an opendatasource command that will work with VFP database tables? That would be even better.
>>>
>>>Thanks
>>
>>Do you have the OleDB installed on that machine?
>>
>>In addition, I think this blog may help
>>
http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/ace>
>Thanks Naomi. When you ask ".. installed on THAT machine?" did you mean the machine holding the database or the machine from which I am running the routine? I am working from my local PC and yes the driver is installed there. The SQL database is installed on a remote server. I don't know if Jet is installed there or not. I am accessing it via SSMS. The server btw is running Server2008 64 bit. I hadn't thought about that difference.
>As an aside, the blog you referred me to lists a series of commands like this:
>
sp_configure 'Ad Hoc Distributed Queries', 1
>reconfigure
>
>The first line is executable in the query window and produces direction to reconfigure. How do you do that if the second line doesn't do it?
You need to have it installed on the server and it should be for 64 bit version.
What do you mean by your second statement? You need to run both commands together - does the second command generate an error?
If it's not broken, fix it until it is.
My Blog