what would the T-SQL be for changing (altering) the data length of a field programmatically in a table in MS Access?
so if a field had a data length of 50, id like to change this to 60 programmatically. What would the code/syntax be?
I have a table with about 300,000 records. About ten fairly small fields per record. I am trying to change the length of a text field from 25 to 40 characters, and I get the error message, 'MS Access can't change the data type. There isn't enough disk space or memory'.
I have never seen this message before. I have about 64 Gig of free disk space. What can I do?
Helo…please really need your help.
I designed a small desktop database to automatically import some Log files. A sample of a transmit log file (emails sent from our rural email stations) looks like this when imported in access.
Date Time Direction SenderMessageID
03062005 133501To InternetemailX@ab.comBlablabla
03062005 125001To InternetemailQ@ab.comBlablabla
03062005 125001To InternetemailZ@ab.comBlablabla
03062005 125001To InternetemailA@ab.comBlablabla
I would like to be able to query all emails sent between one date and another. However, the date here is recorded has a string or text. I would like to automatically add a field with the date as Date/Time datatype in the Table. I think I should be able to do this with a few SQL statement like ALTER TABLE myTable ADD COLUMN NewDate AS datetime…and then another SQL statement to separate data and put it into this new field.
Then I could query for BETWEEN Date1 and Date 2 easily.
I’m pretty close but been trying for a while now and always error messages as results. I think I really NEED help this time.
Thank you,
Ghislain Bob Hachey
I have an access front-end package that works just fine but if there is an update, I just want the program to run scripts to update the SQL Server tables that it is connected to. I just don't know how to do this. I know how to do this on SQL Server Management studio and all, I just was hoping I could do it straight from my access front end so that a user could just click a button and update the tables or something.
View 9 Replies View RelatedWe have a database which has a table with 2 fields - here is an example of the data:
Order File Path
240971 cclapps1scannerWorks Orders 2008Works Orders 2008ITL1ITL1TMSPC11.GFD
We basically need to change the file path to read cclapps3 instead of cclapps1 in every record.
I have subform (Datasheet) is there some code e.g. after update, which can adjust size cell to lenght of data?
View 11 Replies View RelatedI have a database that I'm building off of a process that is currently done in excel. I want my percentages calculations to match what I get in excel but I'm finding the numbers to be off by as much as .4%. I'm pretty sure they issue has to do with the precision of its calculations but what the best settings are.
My percent numbers are currently set to the field size "Double" with a percent formatting. My currency numbers are set to the format Currency and decimal places on auto.
I thought I had solved my initial problem of removing the apostrophe character for each zip code field. Most of the fields have data that reads '12345-1234'. I want to remove each (') character and the "-1234" so I end up with a zipcode of 12345.
I created 2 queries for this process. I first run a query with the following statement "Left([ZIP],Len([ZIP])-6)". That gets rid of the "-1234' ".
The I run the second query with this statement "Right([ZIP],Len([ZIP])-6)".
These 2 queries work perfectly if the original zipcode is " '12345-1234' ", but if it is " '12345' ", the entire zipcode is deleted.
I have attached 3 (.jpg)'s to show you what I am talking about.
I have two tables (Access 2010). One with a list of names (List1) and another with a very similar list of names (List2), but they differ in very small ways. For example, List1 might have John Smith, and List2 would have Smith, John L.; and Smith, John. List2 also has a unique ID associated with these names that I need to append to List1.
I need to design a form that will allow me to look up names in List1, and have it return all names that are similar in List2. I then need to be able to choose with record in List2 matches with the List1 entry (based on a few other columns in List2, such as birth date) and have the form add that unique ID to the List1 record.
PS: I am using Access 2010
I want to import a fixed length file into access 2003, it can only be imported directly into access 97/2000. This site, link http://www.ams.usda.gov/science/pdp/03data.txt
has the idea, but the problem is I can find the "2003 Data zip file" which this
site is refering to. - I have looked for it on the ms office 2003 cd,but I still
can find it.
Please can someone tell me where I can find de "2003 Data zip file"?
Hi,
I am having trouble importing text records into a simple Access 2002 database. I’d be grateful for any help; it would save me hours and hours of manual cut-and-paste from Word into Access!
The records I want to import are text passages which originate in Word. Within some records you might find more than one paragraph. I want that formatting preserved. I signify the end of a record with a “#” character.
I create an Access database consisting of two fields: a) one called "Item" for the text records, with the data type set to memo so it can handle long text passages, and b) one for the primary key ID, which when prompted I let Access create automatically.
Here’s what happens when I try to import the data to my “Item” field”. First, I cut and paste all my original text from Word into Notepad. Then in Access I go File / Get External Data, and then choose Import, and choose Text as the data type. In the ensuing wizard under the Advanced tab I choose the option for delimitation by a specified character, and make “#” that character, and I speciy that I want the data to go into the "Item" field as memo data type. I then finish the wizard.
Sometimes I get error messages that not all data imports successfully, due to key violations, and when I check, nothing has been imported. Other times I just get a message saying it didn't work out. It's never worked.
Here are the records I'm trying to import from Notepad:
Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
#
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
#
ccccccccccccccccccccccccccccccccccccccccccccc
cccccccccccccccccccccccccccccccccccccccccccccccccc cccccccccccccccccccccccccccccccccccccccccccccccccc ccccccccccccccccccccccccccccc
cccccccccccccccc
#
ddddddddddddddddddddddddddddddddddddddddddd
#
(end of records)
I've tried two other forums but none of the suggestions work. There must be some way to do this other than by manual cut and paste. Any advice?
if i create 100 records with autonumbers as primary keys (record_id)
then deleted 30 at random
could i update the record ids so they become 1-70?
hi,
i have a raw data table built, may i know how to hide the table to not allow users to alter the raw data table?
I use the Tool > Startup option to hide everything but if the users are clever enough, they would able to make that available and try to alter the raw data table which i want to prevent and hope that's not gonna happen.
I need to change the format of a date column from Short Date to a custom format. Does anyone know how I can do that with an ALTER TABLE command in SQL? I know how to alter a column and set the data type, I just can't figure out how to set the format for the date.
View 2 Replies View RelatedHow can I add an additional field to a table based on the output of a query? For example, I have table A with 2 types of records and table B with only 1 type of record. Based on this, I can determine which records in table A are the same type as those in table B. I would however like to be able to flag within table A, those records in table A that are the same as those in table B.
View 1 Replies View RelatedI want to add a column to an existing table using the sql statement Alter table. The new column needs to be a yes/no type. The following code almost works but it only sets the column to a general logical type not specifically the yes/no check box .
DoCmd.RunSQL "Alter Table TblIssues Add [" & Issue & "] logical;"
Any ideas?
"ALTER TABLE MOVIE ADD ( MULTIPLEX NUMBER(3))"
while i'm trying to execute this query ...
Its displayed as 'SYNTAX ERROR IN FIELD DEFINITION'
Pls help me.... :confused:
Hopefully someone can help....
I'm trying to change the name of a column in a table using SQL. I've tried different combinations but always get
"Syntax error in Alter Table statement"
SQL I've tried
alter table tblImport
rename column F1 to [Date];*
alter tblImport
rename column F1 to [Date];
alter table tblImport
rename F1 to [Date];
I've checked this out via some SQL sites & think the first version (*) is correct....can someone throw light on this? I'm using Access2000
Hi,
I am building a stock control database. I have a form called [products]. On this form are two fields. One called [ProdReorder], the other [UnitsOnHand].
[UnitsOnHand] is displayed form a subform called [ProductsSubform].
What I want to know is: What code would I enter (& where) so if the [UnitsOnHand] value is less than the [ProdReorder] value it will display the value in red?
Eg: Reorder value is 5
I have 10 motors on stock (UnitsOnHand) = Text is black.
If I have 4 motors on stock = Text is Red.
How can this be done? any advice welcome. PS. my vba skills are weak.
Hi guys
I am trying despereatly to fulfill this function with a macro
any help will be appreciated
I know this may be easy but the help files isnt helpful- so what else is new ;)
Database- C:sports.mbd
table - tblbasketball
current primary key - ID
new primary key needed- ID, EMPNO
The following codes dont work :(
ALTER TABLE tblbasketball
DROP CONSTRAINT PK_tblbasketball PRIMARY KEY
I have an Access 2010 front end with linked tables via ODBC to a SQL Server back end. Is it possible to add, delete, and otherwise alter the tables on the SQL server from the Access front end? I've tried the following but received an error "cannot execute data definition statements on linked data sources"
DoCmd.RunSQL "alter table [dbo_tblAccountsMvOld] add column [cnt] byte;"
Is there code that will let me do this?
I have a form called Orders.I need to lock this form once an invoice has been printed .
I have a check box on this form ,that if ticked should lock that specif order .
This is my Code:
Private Sub Form_Open(Cancel As Integer)
If Me.lockorder = True Then
Me.AllowEdits = False
End If
End Sub
The problem is it does nothing . I am using the open event of the form .The checkbox is called LockOrder.
I have a form that runs off a query that displays further details of a record in a datasheet when you double click on a row.
The query itself has criteria that looks at the open form and selects the correct record.
My question is can you change the results of the query either using vba or a built in feature of access without having to use a separate query.
The selection criteria in the query is:
Forms]![Main]![Ordering-Supplier]![Ordering-Order List].[Form]![suppOrderID]
Basically I want to override the resulting data with another record when I run an event on the form.
I am attempting to update an oracle table using MS Access.
One of the fields to be updated is a primary key number field.
I'd like to use the autoincrement field to update that field.
My sql is:
Code:
Alter table testAutoNum Add column progid autoincrement (1001,2)
This sql nicely creates the new field in the table, but it doesn't start at 1001 and increment by 2; it starts and 1 and increments by 1.It is also creating an autonumber and I need it to be a number.
What am I doing wrong?
I have some code that creates a table based off another table. This code works perfect but Im trying to alter my ID field and change it to an auto increment. How can I fix my code so that it alters my ID field?
Code:
Private Sub Command0_Click()
'OBJECTIVE: to build a table by extracting some fields from a main database
Dim rst As Recordset
Dim strSQL As String
Dim strSQL2 As String
Dim intCount As Integer
[Code] ....
I need to alter filed 24-gen-64, in 24/01/64... how to?
View 5 Replies View Related