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

Create Access table structure from SQL db but import bcp file

Hello Experts,

I have a slow (dialup) link to the network where my SQL 7.0 server resides. In certain situations I have the ability to have the table data sent to me via a fast link in individual tab-delimited bcp text files.

It is sometimes useful to make a local "snapshot" of the data in an Access MDB.

I would like to create a VB utility which will do the following:
1. Create a new mdb with blank tables of the appropriate structure by reading the SQL table structure over the slow link.
2. Import the bcp text data into these tables. The bcp files are named in a consistent manner based on the SQL table names.

Any ideas on how this could be done?

  • 6
  • 4
  • 2
  • +2
1 Solution
Well, if what you have to do is export the structure i'd suggest you to use ADOX (you will have to investigate it by yourself because I don't have any code writen about it), but it would be easier if you have an empty copy of the .mdb in the machine and just copy it.. about importing the data i don't have very much experience working with access on this matter .. SQL Server 7.0 has an object called SQLDMO with which you can alter the database, do bulk copies, etc. very easily, perhaps Access have something like that, but i'm not sure..
slpatchesAuthor Commented:
I was looking for at least some limited sample code. For example, how to instantiate the appropriate ADOX object and set the key properties. I can increase the points if someone says they have the code but 100 pts isn't enough.

Keeping a blank copy of the MDB isn't an option, because there are about 180 tables and they change structure slightly over time and new ones get added.
You can create Access tables via code using DAO (or ADO, but I'm not there yet).  Here's some DAO code that creates a table and adds some fields based on a Word document.  You could modify this code to read the field names from other sources, such as your linked SQLServer table or your tab-delimited text file:

Function ImportWordDoc()
  Dim x As Variant, y As Variant, i As Variant, j As Variant
  Dim tabtext As String
  On Error GoTo Errlabel

  Dim db As Database, t As TableDef, wordobject As Object, f As _
  Dim r As Recordset, filename As String

  ' Get Word Merge Document to Import into Microsoft Access.
  filename = InputBox("Enter the Word Document With its Path, to Import", "Enter FileName", "C:\MY DOCUMENTS\")
  x = InputBox("Enter the Number of Columns", "Enter # of Columns")
  y = InputBox("Enter the Number of Rows", "Enter # of Rows")

  If x = "" Or y = "" Or Dir(filename) = "" Then
    MsgBox "You must supply a valid filename, and the number of table columns and rows."
    Exit Function
  End If

  Set db = CurrentDb()
  Set t = db.CreateTableDef("IMPORT WORD TABLE")    'Remove this line if target table already exists
  Set wordobject = CreateObject("Word.Basic")
  wordobject.fileopen filename

  'Create Field Names.
  For i = 0 To x - 1                                            'Remove this line if target table already exists
    Set f = t.CreateField(wordobject.Selection(), DB_TEXT)      'Remove this line if target table already exists
    t.Fields.Append f                                           'Remove this line if target table already exists
    f.AllowZeroLength = True                                    'Remove this line if target table already exists
    wordobject.nextcell                                         'Remove this line if target table already exists
  Next i                                                        'Remove this line if target table already exists

  'Append Table to Database Table Collection.
  db.TableDefs.Append t                                         'Remove this line if target table already exists

  ' Append records from Word table into Microsoft Access table,
  Set r = db.OpenRecordset("IMPORT WORD TABLE")
  For j = 2 To y + 1
    For i = 0 To x - 1
       tabtext = wordobject.Selection()
       ' Remove any carriage returns in the table cells.
       While InStr(1, tabtext, Chr$(13)) <> 0
          tabtext = Left$(tabtext, InStr(1, tabtext, Chr$(13)) - 1) & _
          Right$(tabtext, Len(tabtext) - InStr(1, tabtext, Chr$(13)))
       r.Fields(i) = tabtext
    Next i
  Next j
  Exit Function

  MsgBox Error
  Exit Function

End Function

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Why don't you just use disconnected recordsets with the Open / Save methods?
TimCotteeHead of Software ServicesCommented:
Example of using ADOX to create a database, table and fields:

Add a reference to ADO for DDL and Security to the project, then add this code, change the database to reflect your real situation, you can also change the field and table names as required to build your database.

    Dim catAccess As New ADOX.Catalog
    Dim tblValExp As New ADOX.Table
    Dim cnnAccess As New ADODB.Connection
    Dim rstValExp As New ADODB.Recordset
    catAccess.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb"
    catAccess.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb;Jet OLEDB:Engine Type=4;"
    With tblValExp
        .Name = "tblValExp1"
        .Columns.Append "Stock Name", adVarWChar, 40
        .Columns.Append "Stock Symbol", adVarWChar, 6     '1
        .Columns.Append "Stock Price", adSingle       '2
        .Columns.Append "Est Date", adVarWChar, 10           '3
        .Columns.Append "Est Year", adVarWChar, 6         '4
        .Columns.Append "Price High(0)", adSingle     '5
        .Columns.Append "Price High(1)", adSingle     '6
        .Columns.Append "Price High(2)", adSingle     '7
        .Columns.Append "Price High(3)", adSingle     '8
        .Columns.Append "Price High(4)", adSingle     '9
        .Columns.Append "Price High(5)", adSingle     '10
    End With
    catAccess.Tables.Append tblValExp
    Set tblValExp = Nothing
    Set catAccess = Nothing
    cnnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb;Persist Security Info=False"
    rstValExp.Open "SELECT * FROM tblValExp1", cnnAccess, adOpenKeyset, adLockOptimistic
    rstValExp.Fields(0).Value = "Name"
    rstValExp.Fields(1).Value = "Symbol"
    rstValExp.Fields(2).Value = 1.5
    rstValExp.Fields(3).Value = Format(Now(), "dd/mm/yyyy")
    rstValExp.Fields(4).Value = "2000"
    rstValExp.Fields(5).Value = 1.5
    rstValExp.Fields(6).Value = 1.5
    rstValExp.Fields(7).Value = 1.5
    rstValExp.Fields(8).Value = 1.5
    rstValExp.Fields(9).Value = 1.5
    rstValExp.Fields(10).Value = 1.5
    Set rstValExp = Nothing
    Set cnnAccess = Nothing

slpatchesAuthor Commented:
This code makes sense to me, but how would I make the structure of the SQL server table drive this function? (step 1)
TimCotteeHead of Software ServicesCommented:
slpatches, this example shows how to use the openschema method to get the structure of a SQL table:

    Dim cnnSQL As ADODB.Connection
    Dim rstTable As ADODB.Recordset
    Set cnnSQL = New ADODB.Connection
    Set rstTable = New ADODB.Recordset
    cnnSQL.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<DatabaseName>;Data Source=<ServerName>"
    Set rstTable = cnnSQL.OpenSchema(adSchemaColumns)
    rstTable.Filter = "TABLE_NAME = 'MyTable'"
    With rstTable
            strText = ""
            For intCount = 0 To .Fields.Count - 1
                strText = strText & .Fields(intCount).Name & vbTab & " = " & vbTab & .Fields(intCount).Value & vbLf
            MsgBox strText
        Loop Until .EOF
    End With
    Set rstTable = Nothing
    Set cnnSQL = Nothing
slpatchesAuthor Commented:
I assume the code above creates an Access 2000 database. When I run the code it creates an MDB, but I can't open in in Access 97 and I don't have Access 2000.

I'm having trouble changing the code to create an Access 97 database and I can't find any reference on these functions in the VB ADO documentation.  Can someone tell me how I would modify this code to work with Access 97?
With ADO.. change your Provider to "Microsoft.OLEDB.Jet.3.51" for Access97.. "Microsoft.OLEDB.Jet.4.0" for Access 2000.

If you opt to use DAO.. use version 3.51 for Access97.. DAO 3.60 for Access 2000.
slpatchesAuthor Commented:
wsh2: When I make the changes you suggest, I get a "couldn't find installable ISAM" error on the line that sets the ActiveConnection.
Please tell me which data source you are trying to connect. Pasting some of your code would be helpful too. Here is some background information from MSDN.. <smile>.

Recordset Persistence

With recordset persistence, you can save Recordset data and metadata as a file. Later, use the persisted file to re-create the Recordset object. The persisted file may exist on a local drive, network server, or as a URL on a Web site.

In addition, the GetString method converts a Recordset object to a form in which the columns and rows are delimited with characters you specify.


The Microsoft OLE DB Persistence Provider supports storing a Recordset object in a file with the Recordset object Save method. Later, the persisted file can be restored with either the Recordset object Open, or Connection object Execute methods.

The Recordset object is converted to a form that can be stored in a file. Recordset objects can be stored in the proprietary Advanced Data TableGram (ADTG) format, or the open Extensible Markup Language (XML) format.

Pending changes are saved in the persisted file. Therefore, you can issue a query that returns a Recordset object; edits the recordset; saves it and the pending changes; later, restores the recordset; then updates the data source with the saved pending changes.


Save a Recordset:

Dim rs as New ADODB.Recordset
rs.Save "c:\yourFile.adtg", adPersistADTG

Open a persisted file with Recordset.Open:

dim rs as New ADODB.Recordset
rs.Open "c:\yourFile.adtg", "Provider=MSPersist",,,adCmdFile

Optionally, if the Recordset does not have an active connection, you can accept all the defaults and simply code:

dim rs as New ADODB.Recordset
rs.Open "c:\yourFile.adtg"

Open a persisted file with Connection.Execute:

dim conn as New ADODB.Connection
dim rs as New ADODB.Recordset
conn.Open "Provider=MSPersist"
set rs = conn.execute("c:\yourFile.adtg")

Open a persisted file with RDS.DataControl:

In this case, the Server property is not set.

Dim dc as New RDS.DataControl
dc.Connection = "Provider=MSPersist"
dc.SQL = "c:\yourFile.adtg"

slpatchesAuthor Commented:
Here's the code that's not working. Gives the "can't find installable ISAM" error on the last line.

Dim sMDBPath As String
sMDBPath = "D:\Dev\SQL2MDB\Test.mdb"
Dim catAccess As New ADOX.Catalog
Dim tblValExp As New ADOX.Table
Dim cnnAccess As New ADODB.Connection
Dim rstValExp As New ADODB.Recordset
    catAccess.Create "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & sMDBPath
catAccess.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & sMDBPath & ";Jet OLEDB:Engine Type=4;"
Delete the catAccess.ActiveConnection statement.. as you have already established a connection with catAccess.Create. Additionally, what is with the

& ";Jet OLEDB:Engine Type=4;"

in your
 catAccess.ActiveConnection="Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & sMDBPath & ";Jet OLEDB:Engine Type=4;"


<--- Code Begin --->

Dim strConnect As String
strConnect = "Provider=Microsoft.Jet.OLEDB.3.51;" _
      & "Data Source=D:\Dev\SQL2MDB\Test.mdb"

' Build New Database
Dim cat As New ADOX.Catalog
cat.Create strConnect

' Create Table
Dim strTable As String
strTable = "x" _
   & Format(Now, "yyyymmdd") _
   & Format(Now, "hhmmss")

Dim tbl As New Table
tbl.Name = strTable
tbl.Columns.Append "Field1", adDouble
tbl.Columns.Append "Field2", adDouble
tbl.Columns.Append "Field3", adDouble
cat.Tables.Append tbl

' Close Database
Set Cat = Nothing

<--- Code End --->

slpatchesAuthor Commented:
Thanks! That works perfectly now.

Now I just need to figure out a good way to translate all the SQL field types into Access field types. I might ask that as a separate EE question.

Thanks all. I wish I could divide the points between several people.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now