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

Posted on 2005-03-10
Medium Priority
Last Modified: 2013-12-25
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
Question by:Cybordax
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
LVL 33

Accepted Solution

CarlWarner earned 2000 total points
ID: 13508170
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.
LVL 33

Expert Comment

ID: 13508568
>> 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 (
LVL 33

Expert Comment

ID: 13510287
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.

LVL 33

Expert Comment

ID: 13510334
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.
LVL 33

Expert Comment

ID: 13511625
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.
LVL 33

Expert Comment

ID: 13514307
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.
LVL 16

Expert Comment

ID: 13520186
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
LVL 33

Expert Comment

ID: 13520397
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.
LVL 29

Expert Comment

ID: 13845891
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

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month14 days, 1 hour left to enroll

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question