Solved

Create Access table structure from SQL db but import bcp file

Posted on 2000-04-24
14
1,071 Views
Last Modified: 2012-06-27
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?

-S
0
Comment
Question by:slpatches
  • 6
  • 4
  • 2
  • +2
14 Comments
 
LVL 1

Expert Comment

by:epretti
Comment Utility
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..
0
 
LVL 3

Author Comment

by:slpatches
Comment Utility
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.
0
 
LVL 14

Expert Comment

by:mgrattan
Comment Utility
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 _
          Field
  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
  wordobject.selectcurword

  '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,
  ' IMPORT WORD TABLE.
  Set r = db.OpenRecordset("IMPORT WORD TABLE")
  For j = 2 To y + 1
    r.AddNew
    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)))
       Wend
       r.Fields(i) = tabtext
       wordobject.nextcell
    Next i
  r.Update
  Next j
  Exit Function

Errlabel:
  MsgBox Error
  Exit Function

End Function


0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
Why don't you just use disconnected recordsets with the Open / Save methods?
0
 
LVL 43

Expert Comment

by:TimCottee
Comment Utility
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.AddNew
    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
    rstValExp.Update
    rstValExp.MoveFirst
    Set rstValExp = Nothing
    Set cnnAccess = Nothing


0
 
LVL 3

Author Comment

by:slpatches
Comment Utility
This code makes sense to me, but how would I make the structure of the SQL server table drive this function? (step 1)
0
 
LVL 43

Expert Comment

by:TimCottee
Comment Utility
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>"
    cnnSQL.Open
    Set rstTable = cnnSQL.OpenSchema(adSchemaColumns)
    rstTable.Filter = "TABLE_NAME = 'MyTable'"
    With rstTable
        .MoveFirst
        Do
            strText = ""
            For intCount = 0 To .Fields.Count - 1
                strText = strText & .Fields(intCount).Name & vbTab & " = " & vbTab & .Fields(intCount).Value & vbLf
            Next
            MsgBox strText
            .MoveNext
        Loop Until .EOF
    End With
    rstTable.Close
    cnnSQL.Close
    Set rstTable = Nothing
    Set cnnSQL = Nothing
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 3

Author Comment

by:slpatches
Comment Utility
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?
0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
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.
0
 
LVL 3

Author Comment

by:slpatches
Comment Utility
wsh2: When I make the changes you suggest, I get a "couldn't find installable ISAM" error on the line that sets the ActiveConnection.
0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
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.

Details

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.

Usage

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"
dc.Refresh


0
 
LVL 3

Author Comment

by:slpatches
Comment Utility
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;"
0
 
LVL 14

Accepted Solution

by:
wsh2 earned 100 total points
Comment Utility
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;"

statement?

<--- 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 --->


0
 
LVL 3

Author Comment

by:slpatches
Comment Utility
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.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now