Save xls sheet or RS to mdb?

[ClimoC]

Hello,

diving into cross access/excel stuff here. Well, not really, but I'm using disconnected ADO Record Sets to index my massive worksheet tables (30,000+ rows). The whole thing runs beautifully.

However I now want to be able to merge/pull/export/import different 'Data' sheets around, so for size and future-proofing (as other side of tech might pick these up in the future), I want to save them out as an .mdb

I've gotten my head around record sets pretty well, but have never bothered tinkering with Access type stuff. I'm aware that there's a very simple Access function/method to 'slurp' up entire sheets in one go. Thing is, I don't have Access on the machine, and neither do/will the users of the XL-based 'app' I've made.

I've managed to get this far:

Public Sub CreateAccessDatabase()
'----------------------------------------------------------------
Dim oADOCat As Object
Dim oTable As Object
Set oADOCat = CreateObject("ADOX.Catalog")

oADOCat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & dbpath & ThisWorkbook.Sheets("GridData").Range("B44").Value & ".mdb"

Set oTable = CreateObject("ADOX.Table")

' Create a new Table object.
With oTable
.Name = ThisWorkbook.Sheets("GridData").Range("B44").Value
' Create fields and append them to the
' Columns collection of the new Table object.
With .Columns

        .Append "Field1", adVarChar, 6
        .Append "Field2", adVarChar, 160
        .Append "Field3", adVarChar, 80
'etc etc, 27 columns total


End With
End With

Set oADOCat = Nothing
End Sub

Public Sub AddData()
Dim oConn As Object
Dim oRS As Object
Dim sSQL As String
Dim tbl As String
Dim dbpath As String, dbConnectStr As String
tbl = ThisWorkbook.Sheets("GridData").Range("B44").Value
dbpath = "C:\" & tbl & ".mdb"
   dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath & ";"

Set oConn = CreateObject("ADODB.Connection")

sSQL = "INSERT INTO " & tbl & " SELECT * FROM [$DataA2:AA" & ThisWorkbook.Sheets("Data").Range("A65000").End(xlUp).Row & _
                    "] IN '" & dbpath & "' 'Excel 8.0;HDR=No;'"

With oConn
        .Open = dbConnectStr
        .Execute sSQL
End With

oConn.Close
Set oConn = Nothing
End Sub

After creating the DB file on my C Drive, I then run the 'Adddata', and I get a window pop up saying "Data Link properties" and it creates a new workbook behind it.

Firstly, I don't want to have a window prompt come up (so some parameters I can set upon creation perhaps?), and secondly, when I click "OK" on the window, I get an Excel error "This data source contains no visible tables"

Anyone see where I've gone wrong? I really didn't think this would be that difficult - but I'm clearly struggling!

Thanks
C

 

[ClimoC]

Think I saw the issue, I've added this line, but it now bugs:

oADOCat.Tables.Append oTable

and I get the error bugging on the new line : "Type is invalid"

 

[Norie]

Are you using late-binding?

If you are perhaps you need to use early binding here.

That's really just a guess though, I don't actually think I've seen disconnected recordsets used like this before.

Wait a minute, I just noticed you seem to be using some ADO constants for the field types.

Do you already have a reference to the relevant ADO libraries?

 

[ClimoC]

Norie - I believe so, TBH I'm just bludgeoning examples I've seen into submission.

Option Explicit

Const dbpath As String = "C:\"

Const adBigInt As Long = 20
Const adBinary As Long = 128
Const adBoolean As Long = 11
Const adChar As Long = 129
Const adCurrency As Long = 6
Const adDate As Long = 7
Const adDBTimeStamp As Long = 135
Const adDecimal As Long = 14
Const adDouble As Long = 5
Const adGUID As Long = 72 'ReplicationID
Const adIDispatch As Long = 9
Const adInteger As Long = 3
Const adLongVarBinary As Long = 205
Const adLongVarChar As Long = 201 'Memo (Access 97)/Hyperlink (Access 97)
Const adLongVarWChar As Long = 203 'Memo (Access 2000 (OLEDB))/Hyperlink (Access 2000 (OLEDB))
Const adNumeric As Long = 131 'Decimal (Access 2000 (OLEDB))
Const adSingle As Long = 4
Const adSmallInt As Long = 2
Const adUnsignedTinyInt As Long = 17 'Byte
Const adVarBinary As Long = 204 'ReplicationID (Access 97)
Const adVarChar As Long = 200 'Text (Access 97)
Const adVariant As Long = 12
Const adVarWChar As Long = 202 'Text (Access 2000 (OLEDB))
Const adWChar As Long = 130

Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1

are my constants...

Refs:
-Microsoft ADO Ext. 2.8 for DDL and Security
-Microsoft ActiveX Data Object 2.8 Library
-Microsoft ActiveX Data Object RecordSet 2.8 Library

are the main ones in use in the workbook.

I read an MS article, saying that the Const-types changed in Jet 4.0. So I amended them, and it seems to start working now. I get a new workbook, and it fills the header row with my column headers - but two things happen.

The same "Data Link Properties" window still comes up - 3 times - and it stops me saying it can't complete the Exeuction - " 'Data$' is not a valid name. Make sure it does not include invalid characters or punctuation and that it is not too long. "

I then end up with 1 workbook with the headers and no data, and it for some reason opens a second one the same, before popping up with the same window, and giving me the above error.

For the interim I'm just decided to export them as csv's, and open and store them to disconnected recordsets that way :P

Seems easier. But ultimately not what I want to do