ADOX Problem...?

Jan 30, 2007

I need to rename tables in code VB2005 (access database). On the
forum I found post about ADOX and this code to rename the tables:



Private Sub RenameTables(ByVal sTextToRemove As String)
Dim i As Integer
Dim dbRename As Database
Dim Connect As New PrivDBEngine

dbRename = Connect.OpenDatabase(tbDBPath.Text)
dbRename.CreateTableDef()

For i = 0 To dbRename.TableDefs.Count - 1

If dbRename.TableDefs(i).Name.Length >= sTextToRemove.Length - 1 Then

If dbRename.TableDefs(i).Name.Substring(0, 9) = sTextToRemove Then

dbRename.TableDefs(i).Name = dbRename.TableDefs(i).Name.Substring(9)
End If
End If
Next i

dbRename.Close()
dbRename = Nothing


MessageBox.Show("Tables in " + tbDBPath.Text + " have been renamed.
Rename Access Tables", MessageBoxButtons.OK, MessageBoxIcon.Information)

End Sub



But it doesn't work because i get an error on Dim dbRename As Database Dim Connect As New PrivDBEngine
What I need to do for this to work?

Thank you!

View 3 Replies


ADVERTISEMENT

ADOX And Vista

Dec 8, 2006

We€™re calling ADOX::Table::Keys::Append to make a primary key for a table using the SQLOLEDB provider. We€™re passing an ADOX::Key with Name set to a custom name, Type set to ADOX::adKeyPrimary, and a single column added. For the Append call, the 2nd parameter is ADOX::adKeyPrimary, the third is a vtMissing (VT_ERROR with DISP_E_PARAMNOTFOUND), the last two are empty BSTR€™s.
On XP, this works well (msadox.dll file version 2.81.1117.0). On Vista, this fails with an invalid parameter error (msadox.dll version 6.0.6000.16386).
Has the interface changed in some way, is this functionality not supported, or is it broken?

View 1 Replies View Related

Add New Column Into The Table Using ADO Not ADOX

Jul 19, 2007

Hi Everybody,



I want to add new column 'firstaname' into the existing table "geo" already two columns namely

'salcode' 'lastname'.I run the following code it runs without error, when i opened the database see the structure of table, i didn't find new column in the table "geo"



here is the snippet of code what i am using.






/* FieldsPtr fields;
FieldPtr field;*/

_bstr_t name("firstname1");

//pRstTitles->get_Fields(&fields);

/* pRstTitles->Fields->Append(name, adVarChar , 15, adFldUnspecified);
pRstTitles->CursorLocation = adUseClient ;
pRstTitles->LockType = adLockOptimistic ;
pRstTitles->Open("geo",
_variant_t((IDispatch *)pConnection,true), adOpenStatic,
adLockOptimistic, adCmdTable);*/





pls pls pls help me out .. I need it very urgently.

View 13 Replies View Related

Why Adox Can Not Append AdUnsignedBigInt Field ?

Jan 22, 2008

my code:



Code Block

void AppendTableTest()
{
// Define ADOX object pointers, initialize pointers. These are in ADOX namespace.
_CatalogPtr m_pCatalog = NULL;
_TablePtr m_pTable = NULL;
try {
HRESULT hr = S_OK;
TESTHR(hr = m_pCatalog.CreateInstance(__uuidof(Catalog)));
// Open the catalog
m_pCatalog->PutActiveConnection("Provider='Microsoft.JET.OLEDB.4.0';data source='c:\new.mdb';");
//m_pCatalog->Tables->Delete("MyTable");
TESTHR(hr = m_pTable.CreateInstance(__uuidof(Table)));
m_pTable->PutName("MyTable");
m_pTable->Columns->Append("Column1",adInteger,0);
m_pTable->Columns->Append("Column2",(DataTypeEnum)21,8);
m_pTable->Columns->Append("colBinary",adBinary,8);
//m_pTable->Columns->Append("Column3",adVarWChar,50);
m_pCatalog->Tables->Append(_variant_t((IDispatch *)m_pTable));
printf("Table 'MyTable' is added.");
// Delete the table as this is a demonstration.
//m_pCatalog->Tables->Delete("MyTable");
printf("Table 'MyTable' is deleted.");
}
catch(_com_error &e) {
// Notify the user of errors if any.
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
TRACE(" Source : %s description : %s ", (LPCSTR)bstrSource, (LPCSTR)bstrDescription);
}





but i got a invalid type error. I cannot understand.
why adox can not append adUnsignedBigInt field ?
Somebody can give me a answer? Thanks.

View 1 Replies View Related

Multi Column Primary Key's In Access Using ADOX [c#]

Jan 15, 2008

Hi all,

I have been pulling my hair trying to figure out what the guys at microsoft were thinking when creating the ADOX library. I have an access table that is syncronized with a SQL server. The table has a primary key with two columns [User] and [Program]. The SQL Server has both columns in as the primary key columns and I have a syncronization mechanism that is responsible for several things, one of which is to recreate the Access data structure. All works well for all tables except this one. I have tried to create the multi-column key in several ways, none that worked. Let me show you what I am doing:



CatalogClass catDCDLocal;

Column c;

catDCDLocal = new CatalogClass();

catDCDLocal.let_ActiveConnection(dbAccess.buildConnectionString(Settings.CattDCDLocalPath, Settings.SecurityDBPath, s.UserID, s.Password));


foreach (Table tbl in catDCDLocal.Tables) {
if (tbl.Name == "Users") {


/* This is retarded so need to clean up... Users table has a primary key consisting of 2 columns */

for (int i = tbl.Keys.Count - 1; i >= 0; i--) { //remove the keys

tbl.Keys.Delete(i);

}

for (int i = tbl.Indexes.Count - 1; i >= 0; i--) { //remove the indexes

tbl.Indexes.Delete(i);

}

tbl.Keys.Append("PrimaryKey", KeyTypeEnum.adKeyUnique, "User", "", "");
tbl.Keys[0].Columns.Append("Program", DataTypeEnum.adWChar, 6);

}
}

I have also tried:

tbl.Keys.Append("PrimaryKey", KeyTypeEnum.adKeyUnique, "User", "", "");

//tbl.Keys[0].Columns.Append("Program", DataTypeEnum.adWChar, 6);

Key k = tbl.Keys[0];

Column col = tbl.Columns["Program"];

//col.ParentCatalog = catDCDLocal;

k.Columns.Append(col, DataTypeEnum.adWChar, 6);



Nothing works for me ;-(

View 18 Replies View Related

ADOX - Access Table Creation With Nullable Columns.

Oct 29, 2007

I need to programatically create a mdb file which will contain nullable columns. I am using C++ with ADOX for the table creation and ADO to perform the table update.

Although ADOX seems to create the table ok, Table->Columns->Appends does not set the fields as adColNullable as expected.

When I insert data using ADO::Recordset->AddNew the following error occurs :- "The field 'MyTable.Column 2' cannot contain a Null value because the Required property for this field is set to True. Enter a value in this field."

Am I on the right tracks here or do I need to adopt a different approach?

Code block to illustrate the problem :-



Code Block
ADOX::_CatalogPtr m_pCatalog;
ADOX::_TablePtr m_pTable;
ADOX::_ColumnPtr m_pCol1;
ADOX::_ColumnPtr m_pCol2;
ADODB::_ConnectionPtr m_pConn;
ADODB::_RecordsetPtr m_pRs;
//ADOX - Create Data Source
_bstr_t strcnn(("Provider='Microsoft.JET.OLEDB.4.0';Data source = C:\test.mdb"));
m_pCatalog.CreateInstance(__uuidof (ADOX::Catalog));
m_pCatalog->Create(strcnn);

m_pTable.CreateInstance(__uuidof(ADOX::Table));
m_pTable->PutName("MyTable");

m_pCol1.CreateInstance(__uuidof(ADOX::Column));
m_pCol1->Name = "Column 1";
m_pCol1->Type = ADOX::adVarWChar;
m_pCol1->DefinedSize = 24;
m_pCol1->Attributes = ADOX::adColNullable;
m_pTable->Columns->Append(m_pCol1->Name, ADOX::adVarWChar, 24);

m_pCol2.CreateInstance(__uuidof(ADOX::Column));
m_pCol2->Name = "Column 2";
m_pCol2->Type = ADOX::adVarWChar;
m_pCol2->DefinedSize = 24;
m_pCol2->Attributes = ADOX::adColNullable;
m_pTable->Columns->Append(m_pCol2->Name, ADOX::adVarWChar, 24);

m_pCatalog->Tables->Append(_variant_t((IDispatch *)m_pTable));


//ADO - Data Access
m_pConn.CreateInstance (__uuidof(ADODB::Connection));
m_pConn->Open(strcnn,_bstr_t(""),_bstr_t(""),ADODB::adOpenUnspecified);
m_pRs.CreateInstance(__uuidof(ADODB::Recordset));
m_pRs->Open("MyTable", _variant_t((IDispatch *)m_pConn,true),ADODB::adOpenKeyset, ADODB::adLockOptimistic, ADODB::adCmdTable);

// Define a SafeArray that contains field names.
SAFEARRAY * psaFields;
SAFEARRAYBOUND aDimFields[1];
aDimFields[0].lLbound = 0;
aDimFields[0].cElements = 1;
psaFields = SafeArrayCreate(VT_VARIANT, 1, aDimFields);

// Create a SafeArray for values.
SAFEARRAY * psaValues;
SAFEARRAYBOUND aDimValues[1];
aDimValues[0].lLbound = 0;
aDimValues[0].cElements = 1;
psaValues = SafeArrayCreate(VT_VARIANT, 1, aDimValues);
long ix[1];
_variant_t var;

//Insert Data
ix[0] = 0;
var = "Column 1";
SafeArrayPutElement(psaFields, ix, (void*) (VARIANT *) (&var));
ix[0] = 0;
var = "test data row 1 col 1 Only";
SafeArrayPutElement(psaValues, ix, (void*)(VARIANT *) &var);
_variant_t vtFields, vtValues;
vtFields.vt = VT_ARRAY | VT_VARIANT;
vtValues.vt = VT_ARRAY | VT_VARIANT;
vtFields.parray = psaFields;
vtValues.parray = psaValues;

m_pRs->AddNew(vtFields, vtValues); //!! Fails Here !!

m_pRs->Update();
m_pRs->Close();
m_pConn->Close();




View 3 Replies View Related

Access Database Datatypes, ADOX And VS2005 Question

Jun 28, 2007



I'm using ADOX 2.8 for table creation: The following is an example of a column defintion:





If CreateNewTable Then CreateNewTable = a.CreateColumn("ReferenceCount", ADOX.DataTypeEnum.adInteger)

If CreateNewTable Then CreateNewTable = a.CreateColumn("Document", ADOX.DataTypeEnum.adLongVarBinary) 'Oleobject

If CreateNewTable Then CreateNewTable = a.CreateColumn("EntityID", ADOX.DataTypeEnum.adWChar, 18) 'text



Where CreateColumn looks like this:


Public Function CreateColumn(ByVal ColumnName As String, ByVal Datatype As ADOX.DataTypeEnum, Optional ByVal Size As Integer = 0) As Boolean

'ADOX.CreateColumn - Called by Common.CreateNewTable
'CreateColumn creates a column described in the Table object so it assumes it is set.
'One method of setting it is to call Select Table after opening the database

If Not Me.ConnectionIsOpen Then
MsgBox("CreateColumn - Failed to Create Column : " _
& ColumnName, MsgBoxStyle.Exclamation, cNoConn)
Return False
End If

Dim col As New ADOX.Column
col.Name = ColumnName
Try
col.Type = Datatype
Catch e As Exception
MsgBox("CreateColumb - Failed to Create Column : " _
& ColumnName, MsgBoxStyle.Exclamation, e.Message)
col = Nothing
Return False
End Try

If Size <> 0 Then col.DefinedSize = Size
Try

Table.Columns.Append(ColumnName, Datatype)

Catch e As Exception
If Err.Number() <> 0 Then
MsgBox(Err.Source & "-->" & Err.Description, , "Error")
End If
MsgBox("CreateColumb - Failed to Append Column : " _
& ColumnName, MsgBoxStyle.Exclamation, e.Message)
Return False
End Try
col = Nothing
Return True
End Function


in CreateColumn("EntityID", ADOX.DataTypeEnum.adWChar, 18)

the 18 specifies the field width in the database. Yet no matter whether I use adWChar or

adVarWChar, Access always shows the field size to be 255.



Does anyone know why or how to fix that?

View 1 Replies View Related

Trouble Using ADOX To Create Linked Tables In Jet Database From An ODBC Datasource

Jun 5, 2007

Hai,

I am using ADOX to create linked tables in a jet database from an ODBC datasource.
The tables in the ODBC data source does not have a primary key.
so I am only able to create read only linked tables.But I want to update the records also.
I tried adding a primary key column to the linked table while creating the link.
but I am getting an error while adding the table to the catalog.

The error message is "Invalid Argument".

I use the following code for creating the linked table

Sub CreateLinkedTable(ByVal strTargetDB As String, ByVal strProviderString As String, ByVal strSourceTbl As String, ByVal strLinkTblName As String)

Dim catDB As ADOX.Catalog
Dim tblLink As ADOX._Table

Dim ADOConnection As New ADODB.Connection

ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strTargetDB & ";User Id=admin;Password=;")

catDB = New ADOX.Catalog

catDB.ActiveConnection = ADOConnection

tblLink = New ADOX.Table

With tblLink

' Name the new Table and set its ParentCatalog property
' to the open Catalog to allow access to the Properties
' collection.
.Name = strLinkTblName
.ParentCatalog = catDB

' Set the properties to create the link.
Dim adoxPro As ADOX.Property

adoxPro = .Properties("Jet OLEDB:Create Link")
adoxPro.Value = True

adoxPro = .Properties("Jet OLEDB:Link Provider String")
adoxPro.Value = strProviderString

adoxPro = .Properties("Jet OLEDB:Remote Table Name")
adoxPro.Value = strSourceTbl


End With

'Adding primary key,
'***** the source column name is "Code" ******
tblLink.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "Code")

'Append the table to the Tables collection.
'******The exception occurs on the following line***********
catDB.Tables.Append(tblLink)

'Append the primary index to table.
catDB = Nothing

End Sub

If I avoid the line for adding the primary key,everything works fine,but the table ctreated is readonly.

Thanks in advance
Sudeep T S

View 4 Replies View Related

Multiple-step OLE DB Operation Generated Errors. Adding Colums Using ADOX

Nov 26, 2007

Hi
I'm trying to add columns to an existing table using ADOX. I'm using C++/CLI. My code is as follow

Table->default->Append("new_column", ADOX:: DataTypeEnum::adWChar, 255);
Table->default["prev_instance_id"]->Properties["Jet OLEDB:Compressed UNICODE Strings"]->default = true;

The first line of code works fine but when I try to set the value of the property I get the following excpetion:

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in MyDll.dll
Additional information: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Any idea?

Marco

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved