|
As far as I know MS Access doesn't have any DML DDL statements like CREATE TABLE. The closest you'll get it reading the Msys... hidden tables.
If you go into the design view of the table, you will see the various options per field.
I think Access allows zero length by default. Remember that zero length and null are different.
Cheers,
Simon
sig ::
"Don't try to be like Jackie. There is only one Jackie.... Study computers instead.", Jackie Chan on career choices.
article :: animation mechanics in SVG blog:: brokenkeyboards
"Most of us are programmers, but a few use VB", Christian Graus
|
|
|
|
|
Thanks, but I do create my tables with CREATE TABLE (OleDbCommmand.ExecuteNonQuery) and the problem is that I afterwards have to go into access and alter the settings from there.
Allowing null I have no problems with. /Robert
|
|
|
|
|
Hi,
I tried to install MSDE2000 in my PC by downloading sql2kdesksp3.exe from microsoft. after installation, when i tried to access the databases through osql tool, it is displaying the following error:
[Shared Memory]SQL Server does not exist or access denied.
[Shared Memory]ConnectionOpen (Connect()).
when i checked in AdministrativeTools-> Services, i could see MSSQL$Myinstance running. even i re-started that service, there is no use.
Can anybody please help
|
|
|
|
|
Use your machine name inplace of localhost while using osql commands
|
|
|
|
|
By default MSDE with SP3 allows only Windows authentication and does not enable any network protocols. The shared memory protocol should work.
To re-enable network protocols, use the Server Network Configuration utility.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hello there,
With an array list containing structures that I want to insert into the database, I attempted to use BULK INSERT in insert all the data at once (this is faster than a loop of INSERTs right?).
However, can I do a BULK INSERT without first writing the data into a file?
Thanks,
Rafferty
|
|
|
|
|
Hm.. no one's replying.. does this mean that there's no other way to do this?
maybe there's another command (other than BULK INSERT) that can accomplish such a task.
|
|
|
|
|
Hello All,
I am implementing merge replication over internet, having following scenario
.
Publisher has some 14 tables in the database out of which only 7 are to be p
ublished, because only 7 tables are having information which will be changed
hence no need to publish.
At the subscriber side also there will be 14tables and out which 7(published
by publisher) will be receive updates from the publisher vis synchronizatio
n.
Now come to the problem:-
When I am trying to apply initial snapshot its it trying to drop existing ta
bles (which I don't want), since table contain froeign key constraint(I came
to know this by seeing output file of merge agent) and because of master-de
tail relation it is not able to drop it and giving error.
I don't want dropping and recreation of the tables in the databse as I am ha
ving correct schema at the subscriber side can anyone help me???
RuchirDhar Dwivedi
Software Engineer
Windowmaker Software Pvt.Ltd.
Baroda, India.
|
|
|
|
|
In my application, I create new databases and tables in the MSDE server. After inserting all the records into the tables I want the user to be able to view the data in Access. This I would like to automate instead of having the user open up access and doing the import themselves. Is this possible? And how is that done?
Now what is the best way to import the data? By creating an access project (.adp) or a database (.mdb)? I would like the user to be able to copy the file to a disk and view it on another machine. The problem with creating an Access project is the user will still be able to edit the data. Based on Windows NT Authentication, the user did have the right to write the extracted data in the application to the MSDE server. But after that’s done with, I don’t want them to edit the data. And if project was used, would I still be able to transport the files to be viewed on another machine or it needs to be exported to mdb?
If exporting to mdb, wouldn’t that complicate the automation of the database creation because a DSN must first be created?
Any help would be much appreciated!
|
|
|
|
|
Instead of duplicating the work of creating data in MS Access, why dont you create a small VB application or a macro in EXCEL (just for displaying data). because you dont want the users to edit the data.
|
|
|
|
|
My program is written in Visual C++. I want the users to be able to do complex queries and generate reports. That's why I chose to display it in Access. I guess I can't avoid having the data read-only because it has to be manipulated. How do I proceed with this?
|
|
|
|
|
I did this before, but it was used to copy a few smaller tables into Access instead of very large ones, as you plan to do. I would crete a linked table to the MSDE in Access and then used two queries in Access. The first was a Delete query to remove the data from the table in Access and then an Append query to fill the table with the data from MSDE. I combined the two queries in a Macro (although you could code it as well) and ran the query in the OnLoad event of my Main Form.
The DSN was not an issue, as it was created with the original front end Access.mdb and when I sent out copies of the mdb, the DSN remained with the mdb file. I did not have to create DSNs on other computers.
Not sure if this helps or not.
Marc
|
|
|
|
|
I am going through so old code adding in error checking and i came across this statement
SQL = "SELECT RouteDef.RouteID, RouteDef.StepID, RouteDef.DeviceID, RouteDef.ReqPos INTO " + sTempTable + " FROM RouteDef WHERE (((RouteDef.RouteID)=" + IntToStr(iOldRouteNum) + "));";
The comment before the statement said something about creating a temporary table.
My question is will this SELECT statement work on a SQL Server 200 machine?
-----------------------
Ok now what?
-----------------------
Never mind i found my answer.
Thanks
|
|
|
|
|
I can update regular column with varchar/numeric type, but I when I try to update image data(longblob) in MySQL, but it doesn't work. Can anybody help me on how to do that?
Here is my code for this:
ProducerConn->Open();
ProducerSide->CommandText = "DROP TABLE IF EXISTS MyDB";
ProducerSide->ExecuteNonQuery();
ProducerSide->CommandText = "CREATE TABLE MyDB (ID timestamp(14), ext varchar(5), image longblob)";
ProducerSide->ExecuteNonQuery();
String * FILE = "image.jpg";
FileStream * tfs = new FileStream(FILE, FileMode::Open, FileAccess::Read);
Int16 len = (int)tfs->Length;
Byte buffer[] = new Byte[len];
tfs->Read(buffer, 0, buffer->Length);
// ProducerSide->CommandText = "update MyDB set image=buffer"; <---- line with problem
ProducerSide->CommandText = "UPDATE MyDB set ext='jpg'";
ProducerSide->ExecuteNonQuery();
Thanks!
|
|
|
|
|
Dear Sir ,
How can i restore DB created by SQL Server 2000 , on machine that has not SQL server licence..
is there any thing as MDAC that should be installed to make access possible from a program to specific DB created by MSAccess
thank you ..
|
|
|
|
|
If the database is smaller than 2GB you could use MSDE[^]
and restore it using the command line OSQL utility.
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
u mean, u try to give access to any client to MS SQL Server. If so u have to install MS SQL Agent, (Desktop Client Version it is available in MS SQL SERVER CD)
if u dont, is what ,u wanna do, to restore a MS SQL Database to a new Server which doesnt have any licence MS SQL Server??? If so, u DONT.
|
|
|
|
|
I have a VC6 project that access' a visual fox pro database via the use of ADO. When I run the project in DEBUG mode everything works fine. When I run it in RELEASED mode on a development machine it also works fine. But when I try running it on a test machine it blows up.
There are multiple tables that I read in. I connect, create a Recordset, iterate through the DB (reading the field values as I go along), close the Recordset, close the connection, and then repeat the process for all other tables. Each table that gets imported has it's own method.
The results are weird as well. The first table will come in fine with no errors or exceptions.
Usually it will hang in the DisconnectADO() method that gets called at the end of the ImportSecondDB() method. But sometimes an exception is thrown that gets caught in the
catch(...). I'm not really sure what's going on here and have already lost close to a week's worth of development time on this. I have provided some code "snipets" bellow. Any help, comments, or suggestions will be most welcomed and greatly appreciated.
HRESULT ClassName::Run()
{
HRESULT hr = S_OK;
for (int i=0; i<2; ++i)
{
switch(i)
{
case 0:
hr = ImportFirstDB();
break;
case 1:
hr = ImportSecondDB();
break;
default:
ASSERT(FALSE);
hr = E_FAIL;
}
}
}
HRESULT ClassName::ImportFirstDB()
{
ado::_ConnectionPtr spConnection;
ado::_RecordsetPtr spRecSet;
CString ostr;
HRESULT hr = S_OK;
_variant_t ovField;
try
{
if (SUCCEEDED(hr = ConnectADO(spConnection, spRecSet)))
{
if (SUCCEEDED(hr = spRecSet->Open(L"SELECT * FROM daily", _variant_t((IDispatch*)spConnection, true), ado::adOpenStatic,ado::adLockReadOnly, ado::adOptionUnspecified)))
{
if (SUCCEEDED(hr = spRecSet->MoveFirst()))
{
while(SUCCEEDED(hr) && !spRecSet->GetadoEOF())
{
ovField = spRecSet->GetCollect("col name");
if (ovField.vt != VT_NULL)
{
ostr = (_bstr_t)ovField;
}
hr = spRecSet->MoveNext();
}
}
}
}
}
catch(_com_error& oE)
{
hr = ProcessError(&oE);
}
catch(...)
{
hr = ProcessConnectionError(spConnection);
}
DisconnectADO(spConnection, spRecSet);
return hr;
}
HRESULT ClassName::ImportSecondDB()
{
ado::_ConnectionPtr spConnection;
ado::_RecordsetPtr spRecSet;
CString ostr;
HRESULT hr = S_OK;
_variant_t ovField;
try
{
if (SUCCEEDED(hr = ConnectADO(spConnection, spRecSet)))
{
if (SUCCEEDED(hr = spRecSet->Open(L"SELECT * FROM daily", _variant_t((IDispatch*)spConnection, true), ado::adOpenStatic,ado::adLockReadOnly, ado::adOptionUnspecified)))
{
if (SUCCEEDED(hr = spRecSet->MoveFirst()))
{
while(SUCCEEDED(hr) && !spRecSet->GetadoEOF())
{
ovField = spRecSet->GetCollect("col name");
if (ovField.vt != VT_NULL)
{
ostr = (_bstr_t)ovField;
}
hr = spRecSet->MoveNext();
}
}
}
}
}
catch(_com_error& oE)
{
hr = ProcessError(&oE);
}
catch(...)
{
hr = ProcessConnectionError(spConnection);
}
DisconnectADO(spConnection, spRecSet);
return hr;
}
HRESULT ClassName::ConnectADO(ado::_ConnectionPtr& spConnection, ado::_RecordsetPtr& spRecSet)
{
CString ostrConnection;
HRESULT hr = S_OK;
try
{
if (SUCCEEDED(hr = spConnection.CreateInstance(__uuidof(ado::Connection))))
{
ostrConnection = _T("Provider=VFPOLEDB.1;");
ostrConnection += _T("Data Source=");
ostrConnection += ostrInputFile + _T(";");
if (SUCCEEDED(hr = spConnection->Open(_bstr_t(ostrConnection), L"", L"", ado::adModeUnknown)))
{
spRecSet.CreateInstance(__uuidof(ado::Recordset));
spRecSet->CursorLocation = ado::adUseClient;
}
else
{
_com_issue_error(hr);
}
}
catch(_com_error &oE)
{
hr = ProcessError(&oE, _T("AccountView_WINTask::ConnectADO() - InputFile: ") + ostrInputFile);
}
catch(...)
{
TRACE( "*** Unhandled Exception ***" );
hr = E_FAIL;
}
return hr;
}
HRESULT ClassName::DisconnectADO(ado::_ConnectionPtr& spConnection, ado::_RecordsetPtr& spRecSet)
{
ASSERT(spConnection);
ASSERT(spRecSet);
HRESULT hr = S_OK;
try
{
if (spRecSet)
{
if (spRecSet->State == ado::adStateOpen)
{
hr = spRecSet->Close();
}
}
if (spConnection)
{
if (spConnection->State == ado::adStateOpen)
{
hr = spConnection->Close();
}
}
spRecSet.Release();
spConnection.Release();
}
catch(_com_error& oE)
{
hr = ProcessError(&oE);
}
catch(...)
{
hr = ProcessConnectionError(spConnection);
}
return hr;
}
|
|
|
|
|
Are you sure you wont get an empty recordset for any of the tables?
calling MoveFirst() on an empty recordset should throw an exception...
as will calling MoveNext when you have reached the end of the recordset.
If I recall correctly, ADo returns the recodset positioned on the first record if there are any, so the movefirst should not be needed, and your while loop should chech that you have not reached EOF. If the recordset is empty, EOF will already be true, as will BOF.
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
Hi There,
I've written an inline table-valued function in SQL such as the following:
ALTER FUNCTION dbo.GetCityByID( @CityID int)<br />
RETURNS TABLE<br />
AS<br />
RETURN(<br />
SELECT<br />
Name,<br />
Url<br />
FROM Cities<br />
WHERE (CityID = @CityID) )<br />
suppose that Cities table includes three fields (CityID, Name, Url).
By the way I wrote a store procedure as follow:
ALTER PROCEDURE MyProcedure ( @MyID int)<br />
AS<br />
SELECT<br />
CountryID,<br />
OriginCityID,<br />
DestCityID<br />
FROM<br />
MyTable<br />
WHERE (MyID = @MyID)<br />
The OriginCityID and DestCityID are related to CityID in Cities table. I wanna get the name
and url of each city by its ID through this stored procedue by making relation to Cities table.
so I call GetCityByID function in my stored procedure like this:
ALTER PROCEDURE MyProcedure ( @MyID int)<br />
AS<br />
SELECT<br />
CountryID,<br />
dbo.GetCityByID(OriginCityID),<br />
dbo.GetCityByID(DestCityID)<br />
FROM<br />
MyTable<br />
WHERE (MyID = @MyID)<br />
this procedure dosn't work an returns error.
What's your solution for getting information from Cities table for OriginCityID and DestCityID?
Thank you in advance.
|
|
|
|
|
Majid Shahabfar wrote:
I've written an inline table-valued function
That is your answer. If it return a table, you must treat it as a table.
Since it looks like this should only ever return one row, why not re-write the function to return a single value. If you need to Url value as well write a function for that.
However, it would be much more efficient to just join to the cities table in your stored procedure.
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
tnx for reply,
the OriginCityID and DestCityID are both related to CityID in Cities table.
so I cannot have inner join for both of them. as I know it's possible for just
one CityID (either Origin or DestCityID).
|
|
|
|
|
Majid Shahabfar wrote:
I cannot have inner join for both of them
Yes you can, you make two inner joins but assign them a different alias - then it is like having two tables with duplicate data. Here:
SELECT CountryID, OCity.Name AS OriginCity, DCity.Name AS DestCity
FROM MyTable
INNER JOIN Cities OCity ON OCity.CityID = OriginCityID
INNER JOIN Cities DCity ON DCity.CityID = DestCityID
WHERE (MyID = @MyID)
Does this help?
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
I have a db table column which is of type varchar, but the value stored is date.
eg: 1/23/2005
2/24/2004
3/23/2006 etc.
I want to format this column as follows:
2__1/23/2005
1__1/23/2004
3__2/23/2006
ie, the reformatted value is the sorting index for date. Ie if the column is selected with an order by, the result will be sorted according to the date even though the column type is varchar.
This twisted way is adopted since this column is refered by crystal reports and the report is sorted according to this column.
Any query to format like this.
|
|
|
|
|
Hi,
Try this............
SELECT *
FROM TableName
ORDER BY CONVERT(DATETIME,DateColumn)
Hope this will help u.................:->
Reagrds,
Ritesh
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
