Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

I've tried this code to create a vFpro tabe but nothing happened

I tried the code I produced(below) to create a free standing foxpro table, as I am not using a .dbc.  It doesnt fall over, it runs straight through asthough it works, yet creates nothing in the directory atall?  Please help!!!!!!!  

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim lngRecsAff As Long

On Error GoTo openerror

conn.Open "Driver={Microsoft Visual FoxPro Driver};" & _
    "SourceType=DBF;" & _
    "SourceDB=c:\New Databases\newDB\;" & _
conn.Execute "create table tab1(col1 numeric(8,2))"

With cmd

    .ActiveConnection = conn
    .Prepared = True
    .CommandType = adCmdText
    .CommandText = "CREATE DBF Salesman (SalesID char(6),CustID numeric(8,2))"
End With
Set cmd = Nothing
Set conn = Nothing

Exit Sub

    MsgBox err.Number & ": " & err.Description
  • 7
1 Solution
Does the directory structure for folder c:\New Databases\newDB\ exist?

I just tried a similar exercise with the OLE DB PRovider for VFP and created an active connection within VFP9 to do this--

o.Open('provider=vfpoledb;data source=c:\documents and settings\administrator\my documents\visual foxpro projects\')
o.Execute("create table tab1(col1 numeric(8,2))")

This sequence successfully created the new table tab1.dbf for me.

But, I realize you are trying to use the ODBC driver to get it done-- I just don't have that installed on this WinXP PC.
>> conn.Execute "create table tab1(col1 numeric(8,2))"

Do you really not a have a space between tab1 and that left part of the paranthesis?

Should be:
conn.Execute "create table tab1 (col1 numeric(8,2))"

tab1(   to  tab1 (
I will try something on a different PC soon that has ODBC.

I just wanted to point out that you don't necessarily need to spell out completely the data type when creating a table in VFP.
It ends up only reading the first letter of whatever you type.  But, if you want to continue to do it for yoyr readability, just know that the letters past the first are ignored.

conn.Execute "create table tab1 (col1 numeric(8,2))"

could be
conn.Execute "create table tab1 (col1 n(8,2))"
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

As far as that space item I mentioned earlier, I had it fail one time without the space between the tab1 and the left paren.  But, then later it worked without the space.  Inconsistency is a pain.
I went to a W2K PC that had the ODBC Driver for VFP installed on it.

I generally setup the first few lines you show to create that first table, tab1.dbf.

I used a slightly different connection string where I pointed to a folder location I knew existed.  I then executed the sequence right through where the Execute occurs to create that tab1.dbf file.  It succeeded.

I then went back and put in your part where you tell where you want the table created, "SourceDB=c:\New Databases\newDB\;", and I know for a fact this path with these folders do not exist in an way, shape, or form on my hard drive.  I executed the table creation code and no error was thrown at all.  As far as it looked, everything ran fine.  Then I went to see where it put that table that it supposedly create and found it didn't create the table.  And the reason was obvious to me why it didn't create the table-- it was because the path of c:\New Databases\newDB\ did not exist.  ODBC is not going to create a path for you and, if it can't find it, it will do nothing legitimate because you didn't tell it to do something legitimate.  As I requested earlier, please check to see if that directory actually exists on your PC.  Maybe it's spelled slightly incorrectly or it flat out doesn't exist.  But, I can only guess from this end.  And I do know the sequence works fine if a proper drive and path that exists is used to feed the SourceDB parameter of the connection string.
More inconsistency.  

I tested a slightly different configuration and the connection string seemed to have the SourceDB value ignored altogether as though it didn't exist (even though it complains if I leave it out).  But at least it did successfully create the table.  It just didn't do it where I asked it to do it this time.

Please verify that you didn't get your tab1.dbf written to a different location than your SourceDB location.  If it didn't get written at all, please verify your SourceDB location does exist.
I had a similar problem when exporting SQL Server to a FoxPro file using DTS.  Everything went fine, but no file.  It turned out the file landed in my current directory (C:\Program Files\Microsoft SQL Server\80\Tools\Binn) no matter what I specified for the connection.  Do a search on you machine and see if the file landed elsewhere
It almost seems to me that the SourceDB location is irrelevant if you are creating a new table.  It sends it to what it considers the current and active directory.  The SourceDB location is relevant if you're opening an existing database or free table, since it is pointing directly to something it can already find.  Sadly, documentation on connectivity driver quirks is lacking and you end up running across these things the hard way.
No comment has been added to this question in more than 21 days, so it is now classified as abandoned..
I will leave the following recommendation for this question in the Cleanup topic area:

Answered CarlWarner

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

EE Cleanup Volunteer

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now