Help With Converting Access VBA To Appropriate SSIS Solution

Mar 20, 2007

Hi

I have created a package which imports a unit of measure table. I now want to populate more rows in the same table from itself. In Access VBA the below code extract does the first part of the job I need to do. Can anyone point me in the right direction to convert this into an efficient SSIS solution.
SQLstr =€?SELECT [UOM conv].[Short Item No], [UOM conv].UOM, Count([UOM conv].[UOM 2]) AS [CountOfUOM 2] €œ & _
€œFROM [UOM conv] €œ & _
€œGROUP BY [UOM conv].[Short Item No], [UOM conv].UOM €œ & _
€œHAVING (((Count([UOM conv].[UOM 2]))>1)); €œ

Set RsMoreThanOne = db.OpenRecordset(SQLstr)
With RsMoreThanOne
While Not .EOF
SQLstr = "SELECT [UOM conv].* FROM [UOM conv] WHERE ((([UOM conv].[Short Item No])=" & ![Short item No] & ") AND (([UOM conv].UOM)='" & !UOM & "'));"

Set RsSql = db.OpenRecordset(SQLstr, dbOpenSnapshot)
RsSql.MoveLast
x = RsSql.RecordCount
Set rs = db.OpenRecordset("UOM Conv")
rs.Index = "PrimaryKey"
RsSql.MoveFirst
Item = RsSql![Short item No]
For y = 1 To x
Um1(y) = RsSql![UOM]
Um2(y) = RsSql![uom 2]
Fct(y) = RsSql!factor
RsSql.MoveNext
Next y
For y = 1 To x - 1
For k = 1+y To x
rs.Seek "=", Item, Um2(y), Um2(k)
If rs.NoMatch Then
rs.AddNew
rs![Short item No] = Item
rs!UOM = Um2(y)
rs![uom 2] = Um2(k)
rs!factor = Fct(k) / Fct(y)
rs!calculated = True
rs.Update
Else
If rs!calculated = True Then
rs.Edit
rs!factor = Fct(k) / Fct(y)
rs.Update
End If
End If
rs.Seek "=", Item, Um2(k), Um2(y)
If rs.NoMatch Then
rs.AddNew
rs![Short item No] = Item
rs!UOM = Um2(k)
rs![uom 2] = Um2(y)
rs!factor = Fct(y) / Fct(k)
rs!calculated = True
rs.Update
Else
If rs!calculated = True Then
rs.Edit
rs!factor = Fct(y) / Fct(k)
rs.Update
End If
End If
Next k
Next y

RsSql.Close
.MoveNext
rs.Close
Wend
.Close
End With
db.Close

Regards

ADG

View 3 Replies


ADVERTISEMENT

'Error Converting Data Type Varchar To Int' : Solution

Jul 3, 2007

Here is the solution to this error message if anyone gets one in the future...It took me about two days to figure out.



This error occurs if you save your stored procedure before execute it. Always execute first before you save. If you save before executing, the server will store its own defaults, usually integers.



This error aslo occurs if your datatypes do not match when passing values from code into the variables of a stored procedure.



It also occurs if the datatypes in your sql file do not match those of the original stored procedure.



You can check to see if the file saved in the "Projects" folder matches with the original by doing the following:



Expand Database, Expand Programmability, Expand Stored Procedures, Expand (Your Stored Procedure), Expand Parameters. Read the datatypes that are revealed in the tree.



Then go to File>Open>Projects>(save sql file). Click open.



View the datatypes in the file. If the datatypes in the file do not match the datatypes in the tree, what you must do to correct, as one solution, is delete both the file and stored procedure.



Then create a new stored procedure by right clicking the stored procedure node. Rewrite the store procedure, execute and then save. Everything should be okay.

View 3 Replies View Related

Replace Access With A .NET Front End And SQL Backend Solution.

Jul 21, 2006

My company currently has about 20-25 Ms Access Database that they want toreplace the FE with .net and the BE on SQL.This will be done using Visual Studio 2005. Once the FE is converted to .netand the BE is SQL they all will be accessed through our intranet (sharepoint).I work in Ms Access and intermediate at VBA and just learing SQL through theENTERPRISE MANAGER SCREEN.I am just now looking at what Visual Studio 2005 is, but can some one tell mehow this will all connect?What is the typical route for this process?Ms access to SQL - upsizing wizard or SQL importing?????Ms Access FE to .net - summarize how this is done in visual studio (user facerebuilt) then placed on sharepoint?Can anyone sum this up?--Message posted via http://www.sqlmonster.com

View 4 Replies View Related

Solution In SSIS

Jun 1, 2006

Hi,

When I create a new Project, I am not getting a solution name in the Solution Explorer. It is starting from the Project Name only.

So when I create a Package Deployment Utility, I am not getting a DTSInstall.exe file in the deployment folder.

Pls guide me.

Thanks & Regards,
Prakash Srinivasan

View 3 Replies View Related

Local Access To Replication Logs (was Does Anyone Have A Clever Solution)

Feb 1, 2005

We have a SQL Server database that runs a website on our server, however the company that we are running the website for requested that they have a replicated local version at their business. Everything is working fine.

However they have requested that they have access to the replication logs. They want to have the details of the recent replication details so they can see if changes they have made on their side has been transferre across successfully they also want a table with all the details in of the changes that have ever happened. Can anyone suggest a good way of doing this?

Thanks Ed

View 3 Replies View Related

SSIS Solution Question

Jun 20, 2007

I have been asked to write a script to hit about 50 SQLServer databases to check the individual status of those dbs. and i need help to write this script.

what i need to do is.

1. hit sqlserver#1 and hit all the databases from that box and see whether the sqlserver dbs are
ok or are suspect. if they are anything else other then OK then I send an email via sendfile.
2. hit sqlserver#2 and continue doing this.

is this possible? do anyone of you know how i can do this programatically via an ssis job or have a script that I can use?


I have both SQL2k and SQL2k5

thanks

View 4 Replies View Related

Is SSIS The Solution To Our Problem???

Mar 12, 2007

Hi All,

A small part of our project needs to be customizable for each of our clients. Each of our clients have the concept of a "JOB"; The details of which are held in their own database. When our product is installed it must gather Job information from their database and match the appropriate fields to our generic Jobs table. Once this has been done, we can generate a stored procedure that will periodically poll the clients database for new/updated jobs and transfer the data to our database.

What we really need is to come up with something that has a generic UI that will allow us to connect to any data source, enumerate the databases and their tables so it will allow someone to perform the operations above.

My question is can SSIS assist us in achieving this goal??

Any help on this would be greatly appreciated.

Paul.

View 5 Replies View Related

MSSQL Remote Access: Can't Find Solution After 6 Hours Of Searching...

Jan 4, 2007

Hello,
I'm trying to access the MSSQl 'remotey'. I open telnet and go to 127.0.0.1 1433 but can't. It seems I have done everything I can to fix it, but nothing solves the problem.

My firewall is disabled.

I have TCP/IP enabled in "SQL Server Configuration Manager" for both Client Protocol and protocols for MSSSQLSERVER (Shared Memory and NAmed Pipes are also both enabled). VIA is disabled.

In SQL Services, SQL Server Integration Services, Full Text Searc, SQL Server, SQL Server Analysis Services, and Server Browser are running. SQL Server Agent is disabled.

In Server Surface Area configuration, under Database Engine is running (automatic) and "Remote Connections" is enabled to TCP/IP and named pipes. Analysis Service has Local and Remote connections checked and is running (automatic). SQL Server agent is stopped.

Integration Services is running (Automatic) SQL Server Browser is running. (Automatic)

When I look in the error log, it shows...

Date 1/4/2007 3:21:45 AM
Log SQL Server (Current - 1/4/2007 3:21:00 AM)

Source Server

Message
Server is listening on [ 127.0.0.1 <ipv4> 1434].

Date 1/4/2007 3:21:45 AM
Log SQL Server (Current - 1/4/2007 3:21:00 AM)

Source Server

Message
Server is listening on [ 'any' <ipv4> 1433].

I have tried to telnet to port 1434 and 1433 but nothing happens. Does anyone know what else I need to be able to access that port??

Thanks
Foley

View 3 Replies View Related

SSIS: Error Opening Solution

Jan 12, 2006

I am trying to open the solution and it is giving me error

"The application for the project \.... est.dtproj is not installed

Make sure application for the project type(.dtproj) is installed."

I tried to open any SSIS solution and it is giving me same error.

Anybody has any ideas how to resolve this problem?

View 7 Replies View Related

General SSIS Solution Question

Jun 20, 2007

I need to load Excel files, which have 200+ columns. There is a check for every column, some are simple type and range checking, some checking involve a little bit complex logic that depends on other columns. The legacy problem use a VB code check each column one by one, row by row, and color the problematic cells.

- What's the best practice to do it?
- How to generate a human readable error log in SSIS? the output error log is so hard to read.

Thanks,

View 1 Replies View Related

SQL Server Equivalent To Access UCASE And TRIM (was Solution Needed Urgently!!!)

Feb 23, 2005

Please provide me with this queries solution


"select leadsource_id from Leadsource_Master where trim(ucase(leadsource_name))='" & Address_Module.name_check(Trim(UCase(cmbLeadSource ))) & "'"

It is a query in access, I want to use the same in SQL SERVER 2000 using VB 6.0

View 14 Replies View Related

Understanding SSIS Solution Directory Structure

Apr 8, 2008

Say I do File -> New -> Project and make a new project called RedApple under C:. For simplicy, I chose root, but I would designate location for all my SSIS projects. I also check the box "Create directory for solution".

I see that this creates C:RedApple

Under this, I have another RedApple directory with a .sln and .suo files.
In this 2dn RedApple directory, I have .database, .dtproj, and .dtproj.user files.

I deleted the default package1, so I don't have any .dtsx files yet.

As far as the above files are concerned (.sln, .suo, .database, .dtproj, .dtproj.user), I understand these are some kind of system or meta data files and I shouldn't be messing around with them. And that's as far as I need to concern myself on these files.

As I add packages to my project, I see that it is creating .dtsx files under the 2nd RedApple directory. So, I'm thinking these are the package files, one for each package I see in my solution explorer, correct?

MY QUESTION:
Now, I am looking at another SSIS directory tree for a project that I created before I started to pay attention to all this. In this other directory tree, I see that there is also a bin directory, which I do not yet have in C:RedAppleRedApple. In this bin directory I see more .dtsx files, the same ones as I see in the directory above it, but also other .dtsx files that have the names of other packages that I onoce created and deleted along the development of this particular project. What are these .dtsx files?

View 3 Replies View Related

Migrating From SQLServer2000 To SQLServer2005 SSIS Solution

Mar 3, 2006

Hi,

We need to migrate data from Sqlserver2000 database to Sqlserver2005. The SQLserver2000 DB was poorly Normalized and the DB was redesigned inSqlserver 2005. Both the databases are operational.Currently we have around 90 tables that need to migrate the data from the OLD DB to NEW DB, all the table mappings between the old schema and new schema have been successfully established.

Each Table is treated on its own merit and applying its own business rules which allows the data to be transformed to the new tables.The client wants us to build a SSIS solution that needs to performs the following

a. Import all Look Tables.

b. Import all master Tables ( One time import of some tables data and other tables may need to import every 4 hrs)

c. Import all Transaction tables( One time import and other tables may need to import every 4 hrs of data)



My Question is how to apporoach this scenario of pulling one time lookup, master and transaction tables and scheduling 4 hrs data importing process, how to build a SSIS solution for this type of scenario.

Thanks

Jayadev



View 4 Replies View Related

What Is The SSIS Solution To Matching Columns When Using The Lookup Transformation

Jan 9, 2008

How would you do the following in SSIS?

SELECT a.TestID,
a.TestCode
FROM TableA a
WHERE UPPER(RTRIM(a.TestCode)) IN SELECT (SELECT UPPER(RTRIM(b.TestCode)) FROM TableB b)

Of course the above query is missing a few things but with ETL the where clause UPPER(RTRIM does not appear to be something that has an object or property that I can use in the Lookup.

Please correct and educate me.

View 4 Replies View Related

Integration Services :: Alternate Solution To DTC In SSIS Transactions

Jun 2, 2015

I would like to have my SSIS tasks to be transacted, but due to infrastructure issues in our network, cannot utilize DTC, because our SQL Boxes are situated where the DTC cannot communicate. I know we can use execute SQL task with Bein Trans, but I dont think dataflow and all will not be in scope of this and cannot be rollback in case of any error in the underlying tasks.

View 2 Replies View Related

Unable To Create A SSIS Deployment Utility For A Solution Which Are Having 2 Packages

Dec 28, 2007

Hi All,

I have created a solution which contains only 2 packages say Package1.dtsx and Pakage2.dtsx. I want to create a deployment utility to deploy onto other developers machince. I changed the project properties "CreateDeploymentUtility" to TRUE. When I do the build it is not creating the files in "Deployment" folder. It is saying Rebuild All Failed but the error is not showing.

For more information the 2 packages have 4 indirect configurations from environment variables which are storing the actual config file path.

Am I missing something here?

Thanks.
Venkat.

View 8 Replies View Related

Opening Solution Creates Extraneous Xx.database Files In SSIS Project

Feb 9, 2007

I have a solution with a couple of ssis projects in it.  Everytime I open the solution, Visual Studio creates an extra .database file for the project's existing xxx.database file.  The solution is under VSS control and VS2005 checks out the project and shows the file as a newly added file.

What causes this and how can I prevent this from occurring?

The files contain this:

<Database xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0" dwd:design-time-name="d3ce9653-3ac5-4ee5-85c3-7d60b2e5f109" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ID>Publish</ID>
  <Name>Publish</Name>
  <CreatedTimestamp>0001-01-01T00:00:00Z</CreatedTimestamp>
  <LastSchemaUpdate>0001-01-01T00:00:00Z</LastSchemaUpdate>
  <LastProcessed>0001-01-01T00:00:00Z</LastProcessed>
  <State>Unprocessed</State>
  <LastUpdate>0001-01-01T00:00:00Z</LastUpdate>
  <DataSourceImpersonationInfo>
    <ImpersonationMode>Default</ImpersonationMode>
    <ImpersonationInfoSecurity>Unchanged</ImpersonationInfoSecurity>
  </DataSourceImpersonationInfo>
</Database>
 

There are 2 of us working on the solution and the other fellow does not see this behavior.

The files do not show up in the VS2005 solution explorer.  If they are user specific as I suspect (impersonation info?), then they should not be added to the .dtproj project file.

Thanks!

View 3 Replies View Related

Converting Access SQL To T-Sql

Oct 15, 2007

Any help converting the following sql to T-Sql would be helpful. I created it in Access ant works great but cant get the case to work. Need to put it into a accounting program that uses T-Sql. The purpose it to come up with a new field called STATUS based on key words in the "decoded" column.

Thanks!

Status: IIf([TableName]![ColumnName] Like "*PA'D*","PA'D",IIf([TableName]![ ColumnName] Like "*SOLD*","SOLD",IIf([TableName]![ ColumnName] Like "*DNU*","DNU","ACTIVE")))

View 2 Replies View Related

Converting MS ACCESS Db To SQLExpress

Nov 10, 2005

Is it possible to convert an ms access mdb file to an sqlexpress mdf file?Any help appreciated,Henk Feijt

View 7 Replies View Related

Converting Access Databases To SQL

Apr 18, 2001

what is the proper method of taking a database made in MS Access and converting it so that it runs in SQL Server 7?

View 1 Replies View Related

Converting Access 97 To SQL7

Jul 23, 2001

I am trying to convert the following Access 97 query into transact SQL for SQL Server 7 but can't seem to get the syntax correct.

"UPDATE datInventory INNER JOIN tmpInventoryReport ON (datInventory.DenomID = tmpInventoryReport.Denomination) AND (datInventory.ReportDate = tmpInventoryReport.ReportDate) AND (datInventory.RegionID = tmpInventoryReport.Region) AND (datInventory.ACCID = tmpInventoryReport.Carrier) AND (datInventory.OwnerID = tmpInventoryReport.[Financial Institution])
SET tmpInventoryReport.[Working Inventory] = [datInventory].[WIBalance], tmpInventoryReport.Surplus = [datInventory].[DSBalance];"

Can anyone help?

View 1 Replies View Related

Converting Yes/No, Access Field To SQL

Oct 24, 2000

I did create the field on table as TinyInt. I created an appending query and appended the records to the SQL table. Now I have 0's or 255's in the field.
Shouldn't they be 0's and 1's instead????
What am I doing wrong?
What's the best way to convert the Yes/No fields into SQL, since I want to keep the access front end.
Thanks for any help.

View 2 Replies View Related

Converting Query From Access

Dec 6, 2007

Hi,I would like some help converting an access query to a SQL Server query.The access query is made up of the following and then repeated for each field:SELECT Sum(IIf([gender]='Female',1,0)) AS Female, Sum(IIf([gender]='Male',1,0)) AS Male...FROM dbo.applicants I have tried using the following to test out an alternative, but it brings back the incorrect figure:SELECT COUNT(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS FemaleFROM dbo.applicants I've looked at the table and should get back 350, but only get back 193.But using the following query I get the correct figure:SELECT COUNT(gender) AS FemaleFROM applicantsGROUP BY genderHAVING (gender = 'Female') Although I can't use the above query because I want to also count how many 'Male' applicants there are.How can I do this?Thanks

View 5 Replies View Related

Converting A View From Access

Nov 17, 2014

I'm converting a View from access to Sql and I'm stuck on this IF statement.

IIf([FG_Qtys_1].[CoreQty]=0 Or [FG_Qtys_1].[CoreQty] Is Null,[KitCoreOnHand],[FG_Qtys_1].[CoreQty]);

I know that I could use CASE statement but i keep on getting errors.

My Case Statement:
case when [dbo.FG_Qtys_view].[CoreQty]=0 then dbo.FG_Qtys_Kits.KitCoreOnHand
when [dbo.FG_Qtys_view].[CoreQty] ISNULL then [dbo.FG_Qtys_view].[CoreQty]
end as CoreQty

View 5 Replies View Related

Converting Access To Server

Apr 13, 2015

I'm currently using Management Studio 2008 R2 and struggling as I am very raw to SQL full stop eg a complete newbie to it.

WHERE (((dbo_ED_ATTENDANCE.AttendDateTime)>=Date()-IIf(Weekday(Date(),2)<2,4,2) And (dbo_ED_ATTENDANCE.AttendDateTime)<=Date()-1)

The above criteria is cut from an existing Access query which I am trying to replicate in SQL Management Studio 2008 R2.

View 1 Replies View Related

Converting Access App To Sql Server

Dec 14, 2005

Hope this makes sense.
I am trying to convert an Access based blog app to SQL Server but I'm having some trouble with some SQL.

The sql is as follows:
SELECT *, (SELECT COUNT(*) FROM tblComment WHERE tblComment.blogID = tblBlog.BlogID AND tblComment.commentInclude <> 0) AS TOTAL_LINKS
FROM joinBlog
WHERE BlogIncluded <> 0
ORDER BY BlogID DESC

The access version returns blog entries & the number of comments posted to each entry.

joinBlog is an Access query:
SELECT tblBlog.BlogID, tblBlog.CatID AS tblBlog_CatID, tblBlog.BlogHeadline, tblBlog.BlogHTML, tblBlog.BlogDate, tblBlog.BlogIncluded, tblCategory.catID AS tblCategory_catID, tblCategory.catName
FROM tblCategory RIGHT JOIN tblBlog ON tblCategory.catID = tblBlog.CatID;

I assume I need to make a view out of the Access query, I have done this & that appears to work.

The problem I have is when I try the 1st sql that is in my page with sql server I get the following error:
The column prefix 'tblBlog' does not match with a table name or alias name used in the query.

I can make the following change which returns data but does not attach the blog comment counts to the proper blog entry, instead it returns the total comments in the query:
SELECT *, (SELECT COUNT(*) FROM tblComment,tblBlog WHERE tblComment.blogID = tblBlog.BlogID AND tblComment.commentInclude <> 0) AS TOTAL_LINKS
FROM joinBlog
WHERE BlogIncluded <> 0
ORDER BY BlogID DESC

Can anyone tell me how to convert this for SQL Server? This is my 1st access to sql server attempt.
Thanks.

View 3 Replies View Related

Converting Access To MS SQL Server

Feb 23, 2007

I'd like to convert my Access database table to MS SQL Server 2005 Express.
I have a text field and a memo field.
What are the corresponding datafield types for SQL Server?

thanks.

View 1 Replies View Related

Converting Access Db To Mysql

Jul 20, 2005

Hey people,I have to convert MS Access 2000 database into mysql database, the wholething being part of this project I'm doing for one of my facultyclasses. My professor somehow presumed I knew db's and gave me long listof things to do with that particular database, first thing being thatparticular conversion. Truth is that I don't know a first thing aboutdb's, let alone using mysql... I downloaded mysql form www.mysql.com andstill searching for MS Access 2000 (it doesn't work with 2003 I have,or I don't know how to make it work).Any kind of help will be welcomed and highly appreciated!!!Thanks,Mario

View 17 Replies View Related

Solution!-Create Access/Jet DB, Tables, Delete Tables, Compact Database

Feb 5, 2007

From Newbie to Newbie,



Add reference to:

'Microsoft ActiveX Data Objects 2.8 Library

'Microsoft ADO Ext.2.8 for DDL and Security

'Microsoft Jet and Replication Objects 2.6 Library

--------------------------------------------------------

Imports System.IO

Imports System.IO.File





Code Snippet

'BACKUP DATABASE

Public Shared Sub Restart()

End Sub



'You have to have a BackUps folder included into your release!

Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click
Dim addtimestamp As String
Dim f As String
Dim z As String
Dim g As String
Dim Dialogbox1 As New Backupinfo


addtimestamp = Format(Now(), "_MMddyy_HHmm")
z = "C:Program FilesVSoftAppMissNewAppDB.mdb"
g = addtimestamp + ".mdb"


'Add timestamp and .mdb endging to NewAppDB
f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & ""



Try

File.Copy(z, f)

Catch ex As System.Exception

System.Windows.Forms.MessageBox.Show(ex.Message)

End Try



MsgBox("Backup completed succesfully.")
If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then
End If
End Sub






Code Snippet

'RESTORE DATABASE

Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles

RestoreDB.Click
Dim Filename As String
Dim Restart1 As New RestoreRestart
Dim overwrite As Boolean
overwrite = True
Dim xi As String


With OpenFileDialog1
.Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*"
If .ShowDialog() = Windows.Forms.DialogResult.OK Then
Filename = .FileName



'Strips restored database from the timestamp
xi = "C:Program FilesVSoftAppMissNewAppDB.mdb"
File.Copy(Filename, xi, overwrite)
End If
End With


'Notify user
MsgBox("Data restored successfully")


Restart()
If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If
End Sub








Code Snippet

'CREATE NEW DATABASE

Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles

CreateNewDB.Click
Dim L As New DatabaseEraseWarning
Dim Cat As ADOX.Catalog
Cat = New ADOX.Catalog
Dim Restart2 As New NewDBRestart
If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then
If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then
Exit Sub
Else
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
End If
End If
Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;

Jet OLEDB:Engine Type=5")

Dim Cn As ADODB.Connection
'Dim Cat As ADOX.Catalog
Dim Tablename As ADOX.Table
'Taylor these according to your need - add so many column as you need.
Dim col As ADOX.Column = New ADOX.Column
Dim col1 As ADOX.Column = New ADOX.Column
Dim col2 As ADOX.Column = New ADOX.Column
Dim col3 As ADOX.Column = New ADOX.Column
Dim col4 As ADOX.Column = New ADOX.Column
Dim col5 As ADOX.Column = New ADOX.Column
Dim col6 As ADOX.Column = New ADOX.Column
Dim col7 As ADOX.Column = New ADOX.Column
Dim col8 As ADOX.Column = New ADOX.Column

Cn = New ADODB.Connection
Cat = New ADOX.Catalog
Tablename = New ADOX.Table



'Open the connection
Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet

OLEDB:Engine Type=5")


'Open the Catalog
Cat.ActiveConnection = Cn



'Create the table (you can name it anyway you want)
Tablename.Name = "Table1"


'Taylor according to your need - add so many column as you need. Watch for the DataType!
col.Name = "ID"
col.Type = ADOX.DataTypeEnum.adInteger
col1.Name = "MA"
col1.Type = ADOX.DataTypeEnum.adInteger
col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col2.Name = "FName"
col2.Type = ADOX.DataTypeEnum.adVarWChar
col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col3.Name = "LName"
col3.Type = ADOX.DataTypeEnum.adVarWChar
col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col4.Name = "DOB"
col4.Type = ADOX.DataTypeEnum.adDate
col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col5.Name = "Gender"
col5.Type = ADOX.DataTypeEnum.adVarWChar
col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col6.Name = "Phone1"
col6.Type = ADOX.DataTypeEnum.adVarWChar
col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col7.Name = "Phone2"
col7.Type = ADOX.DataTypeEnum.adVarWChar
col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col8.Name = "Notes"
col8.Type = ADOX.DataTypeEnum.adVarWChar
col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable



Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID")


'You have to append all your columns you have created above
Tablename.Columns.Append(col)
Tablename.Columns.Append(col1)
Tablename.Columns.Append(col2)
Tablename.Columns.Append(col3)
Tablename.Columns.Append(col4)
Tablename.Columns.Append(col5)
Tablename.Columns.Append(col6)
Tablename.Columns.Append(col7)
Tablename.Columns.Append(col8)



'Append the newly created table to the Tables Collection
Cat.Tables.Append(Tablename)



'User notification )
MsgBox("A new empty database was created successfully")


'clean up objects
Tablename = Nothing
Cat = Nothing
Cn.Close()
Cn = Nothing


'Restart application
If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If

End Sub








Code Snippet



'COMPACT DATABASE

Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles

CompactDB.Click
Dim JRO As JRO.JetEngine
JRO = New JRO.JetEngine


'The first source is the original, the second is the compacted database under an other name.
JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program

FilesVSoftAppMissNewAppDB.mdb; Jet OLEDB:Engine Type=5", "Provider=Microsoft.Jet.OLEDB.4.0;

Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5")


'Original (not compacted database is deleted)
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")


'Compacted database is renamed to the original databas's neme.
Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb")


'User notification
MsgBox("The database was compacted successfully")

End Sub

End Class

View 1 Replies View Related

Converting ACCESS And EXCEL Data To SQL

May 14, 2005

Hi,
I have some tables in an ACCESS database, and would like to recreate them in a SQL2005 databse.How may this be done?I am able to create a Data Component with the ACCESS mdb file.
Likewise, how may I convert EXCEL data to SQL2005 table?Thanks.
David
 

View 3 Replies View Related

Converting Access DB Code To Tsql--Help!!!!!

May 3, 2001

We are migrating an access97 database to sql server7.0. there are queries in access which need to be made into tsql queries.And what are the steps to convert access97 to sql7.0 and how do i migrate memo fields to sql7. Is there some method to convert or tool that does that...any help is welcome and thanks in advance.

View 1 Replies View Related

Access 97 DB And SQL Server 7 - Converting Nvarchar To Int

May 9, 2001

I need help converting an nvarchar value to int. I receive a SQL error when running the following query...

"SELECT DISTINCT [Time Cards].TimeCardID, [Time Cards].Status, [Time Cards].Verification, [Time Card Hours].[Date Worked], [Time Card Hours].[Billable Hours], [Time Card Hours].[Billing Rate], [Time Card Hours].[Overtime Hours], [Time Card Hours].[Overtime Rate], Employees.FirstName, Employees.LastName, [Function].[Function], [Time Card Hours].[Invoiced Hours], [Time Card Hours].[Invoiced Overtime], [Time Cards].[30 Day Grace]

FROM [Function] INNER JOIN (Employees INNER JOIN ([Time Cards] INNER JOIN [Time Card Hours] ON [Time Cards].TimeCardID = [Time Card Hours].[Time Card ID]) ON Employees.EmployeeID = [Time Cards].EmployeeID) ON [Function].FunctionID = Employees.FunctionID

WHERE ((([Time Cards].[30 Day Grace])=-1));"

[Time Card Hours - nvarchar, Time Cards - int]

View 2 Replies View Related

Error In Query Converting From Access To SQL

Dec 7, 1999

Hi,

I'm using a database through ODBC in an application designed on Macromedia Drumbeat 2000, and use the following query for one of the segments:

SELECT DISTINCTROW Books.ISBN, Books.Title, Books.Category, Books.Description, Books.Price, Books.Pubdate, Books.Keywords, Books.UnitWeight, Authors.au_lastname, Authors.au_firstname, Authors.au_midname, Publishers.pub_name, Books.Pub_No
FROM Publishers INNER JOIN (Authors INNER JOIN Books ON Authors.au_id = Books.Au_No) ON Publishers.pub_id = Books.Pub_No

It works fine if I use the MS Access version of the database through ODBC, but if I try using the SQL version, I get the following error for this query:

Line 1: Incorrect syntax near '.'. Statement(s) could not be prepared.

Why on earth is this happenning? I'm completely at my wits end...and pointers would be wonderful.

Thanks...

View 1 Replies View Related







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